mysql: left join but select one particular item from the joined table

I need to select a row only from two corresponding tables, for instance,

member table,

member_id   member_name
1           xxx
2           yyy

profile_picture table

image_id   member_id   image_approved
1          1           no
2          1           no
3          1           yes

I want to select one row from member table each time and one approved image from profile_picture. I am using left join but it doesn't work right as it duplicates the selected member when this member has more than 1 uploaded images. the tricky part is I want to select the image which has been approved only and it is always one picture will be approved.

SELECT *
    FROM member 

    LEFT JOIN profile_picture 
    ON profile_picture.member_id = member.member_id

    WHERE member.member_id = '1'

I would like to get this as a output,

member_id   member_name image_id image_approved
1           xxx         3        yes

is it possible?

thanks.

edit:

thanks guys for the suggestions. many of you have suggested using AND - for instance, AND profile_picture.image_approved = 'yes'

it works only when the image is present. but if the image is absent, there is nothing in the output even though it should output like this below if开发者_运维知识库 the image is absent.

member_id   member_name image_id image_approved
1           xxx         null      null

thanks again!

select
  *
from
  member m
  left join profile_picture p on p.member_id = m.member_id
where
  m.member_id = '1'
  and (p.image_approved is null or p.image_approved = 'yes')

image_approved isn't part of the join condition, so I don't put that in the join clause. It's rather part of the row selection condition, so it goes in where. Thinking about what you want, you either want nothing in profile_picture, or the approved row. So, conditions.

Adding the image_approvedcondition to you query gives you the output you need for given inputs.

SELECT *
    FROM member 

    LEFT OUTER JOIN profile_picture 
    ON profile_picture.member_id = member.member_id

    WHERE member.member_id = '1'
          AND image_approved = 'yes'

do you mean:

SELECT * 
    FROM member  
    LEFT JOIN profile_picture  
    ON profile_picture.member_id = member.member_id 
    WHERE member.member_id = '1' 
    AND profile_picture.image_approved = 'yes'

You don't need to use a LEFT JOIN where an INNER JOIN is appropriate. You need a column on profile_picture, right? It seems like all you need is an additional WHERE condition:

SELECT
   *
FROM
   member
   JOIN profile_picture USING (member_id)
WHERE
   member_id = 1
   AND image_approved = 'yes'

Is there a possibility to have more than one column on the right table and you only want to get one? Do you want the last approved image? Add these:

GROUP BY
   member_id
ORDER BY
   image_id

Either accommodate the NULLs in the WHERE clause as Donnie recommended or use a table expression to restrict your 2nd table like this:

Select member.member_id, member_name, image_id, image_approved
From member LEFT Outer Join
     ( Select image_id, member_id, image_approved
       From profile_picture
       Where image_approved = 'yes' ) Images
          on Images.member_id = member.member_id