Questions

SQL Server: Join two tables returning only one row from the second table

Tags:
+
0 Votes
Locked

SQL Server: Join two tables returning only one row from the second table

axesharpeye
TABLE1

ID (PK) Name
1001 Sam

TABLE2

IDadd (FK) Country
1001 USA
1001 UK

My query:

SELECT A.Name, B.Country
FROM TABLE1 A
INNER JOIN (SELECT TOP 1 * FROM TABLE2 WHERE IDadd = A.ID) AS B
ON B.IDadd = A.ID

Basically, the query intends to get one of the records from TABLE2 and join it to TABLE1. However, I can't get it to work.

Any help is highly appreciated!
  • +
    0 Votes
    john.a.wills

    SELECT Name, Country
    FROM Table1 INNER JOIN Table2 ON IDAdd = ID

    I think that's what you mean and what will give you what you want. Get back to us if that's not really what you want.

    +
    0 Votes
    axesharpeye

    Thank you for the reply.

    Your query will return two (2) rows, since there are two records with IDadd = 1001 in TABLE2. What I want is to get only 1 row from TABLE2 and join it to TABLE1 so I could return only a single row.

    +
    0 Votes
    phil

    Why do you only want one row from table 2? I don't understand the purpose of your query.

    +
    1 Votes
    leifnel

    Top 1 does not make sense without order by, you will just get a random row, which might or might not be the first row you entered.

    Here I select the least (alfabetic) country per person
    [pre]
    SELECT A.Name, B.Country
    FROM TABLE1 A
    INNER JOIN (SELECT IDadd,MIN(Name) Name FROM TABLE2 GROUP BY IDadd) AS B
    ON B.IDadd = A.ID
    [/pre]
    It could also simply be
    [pre]
    SELECT ID,MIN(COUNTRY) FROM TABLE1 A
    LEFT JOIN TABLE2 B ON A.ID=B.IDadd
    GROUP BY ID
    [/pre]
    But it will get tedious with more columns in A, as you have to GROUP BY every column in A, you want to display, and will not work if there are more columns in B you want to show (and you want all B's to come from the same row for each A)

    If table2 had several rows per row in table1, and had several columns, like
    IDadd todate Country address
    1001 01-feb-2010 US Hollywood Boulevard
    1001 10-jul-2009 UK Pinewood
    1002 03-apr-2012 DE Alexanderplatz
    1002 25-may-2008 FR Paris

    You first find the (id,date) pairs, and then select the matching rows.
    [pre]
    SELECT A.name,B.todate,B.Country,B.address
    FROM table1 A
    LEFT JOIN table2 B on A.ID=B.IDadd
    LEFT JOIN
    (SELECT IDadd,max(todate) todate FROM table2
    WHERE todate<='01-jan-2012'
    GROUP BY todate) AS Latest_Move
    ON B.IDadd=Latest_Move.IDadd
    AND B.todate=Latest_Move.todate
    [/pre]

    This would get where the persons were living 01-jan-2012
    [pre]
    Sam 01-feb-2010 US Hollywood Boulevard
    Bill 25-may-2008 FR Paris
    [/pre]
    Bills move to Germany is not listed, because it is after 01-jan-2012

    +
    0 Votes
    axesharpeye

    Thanks for your reply! Your first query above got it working.

    SELECT A.Name, B.Country
    FROM TABLE1 A
    INNER JOIN (SELECT IDadd,MIN(Name) Name FROM TABLE2 GROUP BY IDadd) AS B
    ON B.IDadd = A.ID

  • +
    0 Votes
    john.a.wills

    SELECT Name, Country
    FROM Table1 INNER JOIN Table2 ON IDAdd = ID

    I think that's what you mean and what will give you what you want. Get back to us if that's not really what you want.

    +
    0 Votes
    axesharpeye

    Thank you for the reply.

    Your query will return two (2) rows, since there are two records with IDadd = 1001 in TABLE2. What I want is to get only 1 row from TABLE2 and join it to TABLE1 so I could return only a single row.

    +
    0 Votes
    phil

    Why do you only want one row from table 2? I don't understand the purpose of your query.

    +
    1 Votes
    leifnel

    Top 1 does not make sense without order by, you will just get a random row, which might or might not be the first row you entered.

    Here I select the least (alfabetic) country per person
    [pre]
    SELECT A.Name, B.Country
    FROM TABLE1 A
    INNER JOIN (SELECT IDadd,MIN(Name) Name FROM TABLE2 GROUP BY IDadd) AS B
    ON B.IDadd = A.ID
    [/pre]
    It could also simply be
    [pre]
    SELECT ID,MIN(COUNTRY) FROM TABLE1 A
    LEFT JOIN TABLE2 B ON A.ID=B.IDadd
    GROUP BY ID
    [/pre]
    But it will get tedious with more columns in A, as you have to GROUP BY every column in A, you want to display, and will not work if there are more columns in B you want to show (and you want all B's to come from the same row for each A)

    If table2 had several rows per row in table1, and had several columns, like
    IDadd todate Country address
    1001 01-feb-2010 US Hollywood Boulevard
    1001 10-jul-2009 UK Pinewood
    1002 03-apr-2012 DE Alexanderplatz
    1002 25-may-2008 FR Paris

    You first find the (id,date) pairs, and then select the matching rows.
    [pre]
    SELECT A.name,B.todate,B.Country,B.address
    FROM table1 A
    LEFT JOIN table2 B on A.ID=B.IDadd
    LEFT JOIN
    (SELECT IDadd,max(todate) todate FROM table2
    WHERE todate<='01-jan-2012'
    GROUP BY todate) AS Latest_Move
    ON B.IDadd=Latest_Move.IDadd
    AND B.todate=Latest_Move.todate
    [/pre]

    This would get where the persons were living 01-jan-2012
    [pre]
    Sam 01-feb-2010 US Hollywood Boulevard
    Bill 25-may-2008 FR Paris
    [/pre]
    Bills move to Germany is not listed, because it is after 01-jan-2012

    +
    0 Votes
    axesharpeye

    Thanks for your reply! Your first query above got it working.

    SELECT A.Name, B.Country
    FROM TABLE1 A
    INNER JOIN (SELECT IDadd,MIN(Name) Name FROM TABLE2 GROUP BY IDadd) AS B
    ON B.IDadd = A.ID