SQL subquery help

[ultim]

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

 

[suarve]

Something like this should work (don't why you need a subquery to return what you posted) - created tables called LOCATION and USERS:

SELECT USERS.*, LOCATION.Location
FROM USERS INNER JOIN LOCATION ON USERS.UserID = LOCATION.UserID
ORDER BY LOCATION.ListOrder;

 

[Sic]

seeing as that's a one to many, I'd add a group in there as well

SELECT USERS.*, LOCATION.Location
FROM USERS INNER JOIN LOCATION ON USERS.UserID = LOCATION.UserID
GROUP BY UserID
ORDER BY LOCATION.ListOrder;

 

[ultim]

Wouldnt that return mulitple rows for each user?

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

 

[Sic]

hence my amendment, which will only return one user and its first location

 

[ultim]

ok thanks!

Is using Group By my only option?

 

[Sic]

you could try

SELECT DISTINCT USERS.*, LOCATION.Location
FROM USERS INNER JOIN LOCATION ON USERS.UserID = LOCATION.UserID
ORDER BY LOCATION.ListOrder;

but I'm pretty sure it's slower than group by. That's MySQL, though - not sure about SQL

 

[s-p]

I'm not sure how the GROUP BY UserID, will help in this instance? You'll still get two results returned per user, as their location differs. If you weren't returning the location in the query, then you'd only get one result per user.

Depending on the size of the query, I'd use one of the ranking functions (i.e. http://msdn.microsoft.com/en-us/library/ms189798.aspx) that may be available to you. Then based on the rank, apply a condition on this value so you only receive the first result from each userID.

 

[Sic]

Does grouping work differently in SQL to MySQL then? Because in MySQL, grouping this data would only return unique instances of UserID. It doesn't matter what tables you join/return. If so, my apologies for confusion.

 

[Mirage]

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;

 

[ultim]

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?

 

[PsychoDuck]

The above query seems to return UK for both users:-

This is how I would do it:-

If you want the first location:-

select u.*, l.* from [user] u
inner join (select userid, min(listorder) as listorder from location group by userid) minlocation on u.userid = minlocation.userid
inner join location l on u.userid = l.userid and minlocation.listorder = l.listorder

If you want the last location:-

select u.*, l.* from [user] u
inner join (select userid, max(listorder) as listorder from location group by userid) maxlocation on u.userid = maxlocation.userid
inner join location l on u.userid = l.userid and maxlocation.listorder = l.listorder