views:

35

answers:

2

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 on min rank for given user:

id  name  number  max_title  rank
----------------------------------
1   x1    123     't3'        1
2   x2    234     't1'        3

question updated!

A: 
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.

AdaTheDev
There was no mention of what the real relationship was between these tables. But if there is the possiblity of no records in "table2" for an id, then just needs a LEFT JOIN instead.
AdaTheDev
+2  A: 

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
OMG Ponies
Sorry i updated question but you are right for my previous question
rs