CODEWITHSUNDEEP
sqlselectmysqlleft-join
Run
Run

Reputation: 57176

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!

Upvotes: 4

Views: 2187

Answers (5)

Lynette Duffy
Lynette Duffy

Reputation: 404

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

Upvotes: 0

Donnie
Donnie

Reputation: 46913

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.

Upvotes: 2

Explosion Pills
Explosion Pills

Reputation: 191729

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

Upvotes: 0

Randy
Randy

Reputation: 16677

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'

Upvotes: 0

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58431

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'

Upvotes: 0

Related Questions