Post reply

Very Easy SQL JOIN - But weird result

  • July 6, 2016 at 11:40 am

    #327595

    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

    #1888759

    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

    #1888763

    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

    #1888777

    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

    #1888778

    PLEASE SEE ATTACHED WORD DOCUMENT TO GET MORE CLEAR PICTURE OF WHAT I AM TRYING TO ACHIEVE

  • July 6, 2016 at 1:09 pm

    #1888779

    PLEASE SEE ATTACHED WORD DOCUMENT TO GET MORE CLEAR PICTURE OF WHAT I AM TRYING TO ACHIEVE

  • July 6, 2016 at 1:22 pm

    #1888784

    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

    #1888787

    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

    #1888789

    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