1

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.

0

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

| improve this answer | |
0
SELECT A.ID
     , A.FIRST
     , A.LAST
     , A.AGE
     , B.IMAGE
FROM (SELECT id
           , first
           , last
           , TIMESTAMPDIFF(YEAR, dob, NOW()) AS age 
      FROM profile 
      WHERE active = 'Y' ) A
JOIN (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) B
ON A.ID = B.ID
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.