I have two mySQL queries. Individually, they give me what I'm looking for. However, I prefer to combine the results into a single set.
First query. Simple selection from table 'profile'.
SELECT id, first, last, TIMESTAMPDIFF(YEAR, dob, NOW()) AS age FROM profile WHERE active = 'Y'
|id |first | last | age| |160808213759VYO |Peter |Pan | 31 | |160808214828NGO |Lion |King | 30 | |160808215315TRA |Buck |Roger | 29 |
Second query. For a given
id there are multiple entries (description). I
want to pick out the first entry for a given
id only and it's working correctly.
SELECT r.id, (SELECT r1.description FROM reference r1 WHERE r1.id = r.id AND r1.active = 'Y' ORDER BY r1.description ASC LIMIT 1) AS image FROM reference r WHERE r.type = 'I' AND r.active = 'Y' GROUP BY r.id ORDER BY r.id, image;
|id |image | |160808213759VYO| img/160808213759VYO_003.jpg| |160808214828NGO| img/160808214828NGO_002.jpg| |160808215315TRA| img/160808215315TRA_001.jpg|
How can I join these two queries to get this:
|id |first | last | age| image | |160808213759VYO| Peter| Pan | 31 | img/160808213759VYO_003.jpg | |160808214828NGO| Lion | King | 30 | img/160808214828NGO_002.jpg | |160808215315TRA| Buck | Roger| 29 | img/160808215315TRA_001.jpg |
Any help will be much appreciated.