Design query for following scenario
I have two tables - Table1:
id name number
------------------
1 x1 123
2 x2 234
...and Table2:
tbl1id title rank
--------------------
1 t1 3
1 t2 2
2 t1 3
1 t3 1
Is there a way I can join them to return result as showing max title based 开发者_Go百科on min rank for given user:
id name number max_title rank
----------------------------------
1 x1 123 't3' 1
2 x2 234 't1' 3
question updated!
Use:
SELECT t.id,
t.name,
t.number,
COALESCE(x.max_title, 'no title associated') AS max_title
FROM TABLE1 t
LEFT JOIN (SELECT t2.tbl1id,
MAX(t2.title) 'max_title'
FROM TABLE2 t2
WHERE t2.rank = (SELECT MIN(rank)
FROM TABLE2
WHERE tbl1id = t2.tbl1id)
GROUP BY t2.tbl1id) x ON x.tbl1id = t.id
SELECT t1.id, t1.name, t1.number, MAX(t2.title)
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.id = t2.tbl1id
GROUP BY t1.id, t1.name, t1.number
EDIT:
- updated to use LEFT JOIN incase its the case there may not be a record in table2 for a given id.
- answer is now irrelevant as question has changed since the original one. Leaving for history.
精彩评论