July 6, 2016 at 11:40 am
I am trying to join two tables Table A and Table B
Table A has 4 records and Table B has matching 2 record
I want to display all the columns of table A and 1 column from Table B for that matching records.
So my total display of records should be 4 (RESULT SHOULD BE 4) with all columns from table A adding one column for table B.
But for some reason my result displays all records from Table A multiply by matching records of Table B.
So total it displays 8 records.
(so for example if my first table query display 200 records and second table query display 100 matching record with table a, than my result suppose to be 200 records with additional column from table b but my query display results 200*100 (20000 records)
what i am doing it wrong ?
Here is my query
Select TableA.Stud, TableA.Roll, TableB.Class,TableA.sportID from TableA JOIN TableB ON TableA.sportID=TableB.playID where year=2016 and schooID=100
Please help correct this query ASAP
THANKS
July 6, 2016 at 12:10 pm
You are not doing anything wrong, based upon the information you have provided.
If you can supply some sample data, table structures, and the desired output, we may be able to help you.
But, double check your join condition.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
July 6, 2016 at 12:15 pm
That's the way JOINs work. You get a record for every match; so if each of four records matches two records, you will get eight records. If you want fewer results, you'll need some way to return fewer matches such as CROSS APPLY/TOP(1), ROW_NUMBER(), or GROUP BY. Since you've provided very little information, there is no way to determine which approach would be best.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 6, 2016 at 1:07 pm
PLEASE SEE ATTACHED WORD DOCUMENT TO GET MORE CLEAR PICTURE OF WHAT I AM TRYING TO ACHIEVE
Table A
select all_ID,My_Name,account,total,total_split,money_no from misc where all_ID=5505
Result of Table A Query
all_ID My_Name account total total_split money_no
750 Raja co. A55 64 43 31
750 Raja co. A55 64 21 35
Table B
select cid,all_ID,number from method where all_ID=5505
Result of Table B Query
cid all_ID number
A55 750 5
I JOINED TWO TABLES and run the Query
select misc.all_ID,My_Name,account,total,total_split,money_no,
method.number
from misc
join method
on misc.account=method.cid
where misc.all_ID = 5505
By running Join Query I get following result
Technically I should only get the result which is marked as YELLOW:
all_ID My_Name account total total_split money_no number
750 Raja co. A55 64 43 31 3430
750 Raja co. A55 64 21 35 3430
750 Raja co. A55 64 43 31 62
750 Raja co. A55 64 21 35 62
750 Raja co. A55 64 43 31 818
750 Raja co. A55 64 21 35 818
750 Raja co. A55 64 43 31 265
750 Raja co. A55 64 21 35 265
750 Raja co. A55 64 43 31 56
750 Raja co. A55 64 21 35 56
750 Raja co. A55 64 43 31 34
750 Raja co. A55 64 21 35 34
750 Raja co. A55 64 43 31 76
750 Raja co. A55 64 21 35 76
750 Raja co. A55 64 43 31 5
750 Raja co. A55 64 21 35 5
750 Raja co. A55 64 43 31 45
750 Raja co. A55 64 21 35 45
WHAT IS WRONG ??????
July 6, 2016 at 1:08 pm
PLEASE SEE ATTACHED WORD DOCUMENT TO GET MORE CLEAR PICTURE OF WHAT I AM TRYING TO ACHIEVE
July 6, 2016 at 1:09 pm
PLEASE SEE ATTACHED WORD DOCUMENT TO GET MORE CLEAR PICTURE OF WHAT I AM TRYING TO ACHIEVE
July 6, 2016 at 1:22 pm
after i posted the example I NOTICE Something
that Table B (second table) has many records with All_ID column not just one
so basically i want to make sure the result/condition should match both the column of all_id from both table should have exact same ID and account/cid
July 6, 2016 at 1:28 pm
Firstly, according to your data you are selecting where all_ID = 5505 but in your results you have all_ID as 750 so is a bit confusing.
So in both select statements you are selecting using all_ID you haven't included this in your join - so does this give you what you are after?
select misc.all_ID,My_Name,account,total,total_split,money_no,
method.number
from misc
join method
on misc.all_ID=method.all_ID
and misc.account=method.cid
where misc.all_ID = 5505
July 6, 2016 at 1:29 pm
Absolutely nothing is wrong. The join is doing exactly what you are telling it to do.
Please take a look at the link in mine, and Drew's signature that describes how to properly post code.
None of what you are posting makes sense, nor does it match.
But, based on what you posted, you are joining on "account" and "cid". I'm guessing, and it certainly is a guess, that there are far more records with matching values for these fields.
You gave us these queries:
select all_ID,My_Name,account,total,total_split,money_no from misc where all_ID=5505
select cid,all_ID,number from method where all_ID=5505
You need to run this query:
select all_ID,My_Name,account,total,total_split,money_no from misc where account = 'A55'
select cid,all_ID,number from method where CID = 'A55'
I bet that these return a lot more rows.
Like I said in my first post, look at your join condition.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply