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_IDMy_Nameaccounttotaltotal_splitmoney_no

    750Raja co.A55644331

    750Raja co.A55642135

    Table B

    select cid,all_ID,number from method where all_ID=5505

    Result of Table B Query

    cidall_IDnumber

    A557505

    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_IDMy_Nameaccounttotaltotal_splitmoney_nonumber

    750Raja co.A556443313430

    750Raja co.A556421353430

    750Raja co.A5564433162

    750Raja co.A5564213562

    750Raja co.A55644331818

    750Raja co.A55642135818

    750Raja co.A55644331265

    750Raja co.A55642135265

    750Raja co.A5564433156

    750Raja co.A5564213556

    750Raja co.A5564433134

    750Raja co.A5564213534

    750Raja co.A5564433176

    750Raja co.A5564213576

    750Raja co.A556443315

    750Raja co.A556421355

    750Raja co.A5564433145

    750Raja co.A5564213545

    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