SQL subquery help

ultim · 21 Dec 2008 at 20:52

Ive got two tables

USERID USERNAME
1 Tom
2 Dick

LOCATIONID USERID LOCATION LISTORDER
1 1 UK a
2 1 Scotland b
3 2 UK e
4 2 USA b

I want the query to return all the user and their 1st location depending on the LISTORDER

It should return
1 Tom UK
2 Dick USA

Can someone help with writing the query please?

Thanks

ultim · 21 Dec 2008 at 21:32

Wouldnt that return mulitple rows for each user?

I just want one row for each user with its prefer location

ultim · 21 Dec 2008 at 22:04

ok thanks!

Is using Group By my only option?

ultim · 22 Dec 2008 at 10:10

Mirage said:
None of these will work, even with group by, the original poster needs a SUBQUERY such as:

SELECT tblUser.UserName, tblLocation.Location
FROM tblLocation INNER JOIN tblUser ON tblLocation.UserID = tblUser.UserID
WHERE tblLocation.Location In (SELECT TOP 1 tblLocation.Location FROM tblLocation ORDER BY tblLocation.ListOrder DESC) AND tblLocation.UserID = tblLocation.UserID;

AND tblLocation.UserID = tblLocation.UserID

Is that a typo?