views:

39

answers:

2
+1  Q: 

Nested SQL Query

I have the following tables:

Club: Club_ID | Title | Created_Date | ...

Club_Intesect: User_ID | Club_ID | Access

I'm trying to select a variable number of clubs, and join the ID of the user with the highest access in that club. This person is considered the owner.

So if Club 100 has Members A, B, C with access 3,4, and 5 respectively: I want the final query to select from club:

 Club.Club_ID     Club.Title        Club.Created_Date  Club_Intersect.User_ID

      100         |  "Test Club"   |  "Creation Date"     |       C             |
      101         | "Test Club 2"  |  "Creation_Date"     | Highest Access User |
      ...
+1  A: 
SELECT * FROM Club c
JOIN Club_Intesect ci ON ci.Club_ID = c.Club_ID
WHERE ci.Access = (SELECT MAX(Access) FROM Club_Intesect WHERE Club_ID = c.Club_ID)

Not tested but you get the idea

colithium
Since the desired result is 3, not 5, you probably need MIN rather than MAX.
Jonathan Leffler
...I think the desired result is the max access (5).
colithium
Actually it is 5, sorry for the original typo.
pws5068
It's ok, it was edited a lot and I was confused too for a bit
colithium
+1  A: 
SELECT * FROM Club WHERE CLUB_ID = (SELECT Club_ID FROM Club_Intersect ORDER BY Access DESC LIMIT 1);

I think that would work for selecting the club you want, using a nested SQL Query. You didn't specify what you wanted in your returned results, because this query would only retrieve the Club columns.

chustar