Questions

Answer for:

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

Message 4 of 6

View entire thread
+
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