Optimize 2 mysql queries to one

Question

User has pets, pets may have pictures One picture may be set as mainpicture (mainpic=0 or mainpic=1)

I need to get all the pet data from the pets table and I also need the picture id where mainpic=1 or first picture or null if he has no picture in the picture table. I need to get the pet data even if it has no picture at all.

Currently I use 2 queries to get this done: SELECT * FROM pets WHERE pets.userId='$userId' ORDER BY pets.created ASC LIMIT 5 ... for each result: SELECT id FROM pictures WHERE petId = '$petId' ORDER BY mainpic DESC LIMIT 1

I need this optimized and in one query.

Thanks, Hamlet

Answer 1

Try this:

SELECT * FROM pets
INNER JOIN pictures ON pictures.petid = pets.id AND pictures.mainpic = 1
WHERE pets.userid='$userid'

Answer 2

use an inner join to omit unmatched records

select * 
from pets inner join pictures 
on pets.id = petId and mainpic=1
where userId=? 
order by pets.created ASC limit 5

if you only want null in the picture related fields but want the pet info (your Q was a little unclear) then use a left join

select * 
from pets left join pictures 
on pets.id = petId and mainpic=1
where userId=? 
order by pets.created ASC limit 5

Answer 3

SELECT *
FROM pets p
     LEFT OUTER JOIN (
        SELECT *
        FROM pictures pict
        WHERE pict.mainpic = 1
     ) pict ON pict.petid = p.petid
WHERE p.userid = '$userid'

Answer 4

SELECT  (
        SELECT  id
        FROM    pictures pi
        WHERE   pi.petid = pe.petid
        ORDER BY
                mainpic DESC
        LIMIT 1
        )
FROM    pets pe