id="info"

Question

Locked

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

By 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!

This conversation is currently closed to new comments.

6 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

You have your parentheses wrong

by john.a.wills In reply to SQL Server: Join two tabl ...

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.

Collapse -

Reponse To Answer

by axesharpeye In reply to You have your parentheses ...

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.

Collapse -

Reponse To Answer

by phil In reply to You have your parentheses ...

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

Collapse -

Which row do you want?

by leifnel In reply to SQL Server: Join two tabl ...

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

Collapse -

Reponse To Answer

by axesharpeye In reply to Which row do you want?

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

Back to Networks Forum
6 total posts (Page 1 of 1)  

Hardware Forums