Mysql – Joining results of two theSQL queries

join;MySQL

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'

Results:

|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;

Result:

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

Best Answer

I figure something like:

SELECT p.id, p.first, p.last, TIMESTAMPDIFF(YEAR, p.dob, NOW()) AS age
     , MIN(r.description) as image 
FROM profile p
JOIN reference r 
    ON r.id = p.id
WHERE r.type = 'I' AND r.active = 'Y' 
  AND p.active = 'Y'
GROUP BY p.id, p.first, p.last, p.dob

;

should work

Related Question