mysql: оставить соединение, но выбрать один конкретный элемент из объединенной таблицы

Мне нужно выбрать строку только из двух соответствующих таблиц, например,

таблица участников,

member_id   member_name
1           xxx
2           yyy

таблица profile_picture

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

Я хочу каждый раз выбирать одну строку из таблицы участников и одно утвержденное изображение из profile_picture. Я использую левое соединение, но оно не работает правильно, поскольку дублирует выбранного участника, когда у этого участника есть более 1 загруженного изображения. сложная часть заключается в том, что я хочу выбрать только изображение, которое было одобрено, и всегда будет одобрено одно изображение.

SELECT *
    FROM member 

    LEFT JOIN profile_picture 
    ON profile_picture.member_id = member.member_id

    WHERE member.member_id = '1'

Я хотел бы получить это как вывод,

member_id   member_name image_id image_approved
1           xxx         3        yes

Является ли это возможным?

Спасибо.

изменить:

спасибо, ребята, за предложения. многие из вас предлагали использовать И — например, И profile_picture.image_approved = 'yes'

он работает только тогда, когда изображение присутствует. но если изображение отсутствует, на выходе ничего нет, даже если оно должно выводиться, как показано ниже, если изображение отсутствует.

member_id   member_name image_id image_approved
1           xxx         null      null

еще раз спасибо!


mysql sql select left-join
person laukok    schedule 29.11.2010    source источник

Ответы (5)


arrow_upward
2
arrow_downward 

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 не является частью условия соединения, поэтому я не добавляю его в предложение join. Это скорее часть условия выбора строки, поэтому она идет в where. Размышляя о том, чего вы хотите, вы либо ничего не хотите в profile_picture, либо в утвержденной строке. Итак, условия.

person Donnie    schedule 29.11.2010
comment
m и p являются таблицами. Ключевое слово as является необязательным и подразумевается, поэтому member имеет псевдоним m, а profile_picture псевдоним p. - person Donnie; 30.11.2010

arrow_upward
0
arrow_downward

Добавление image_approvedcondition к вашему запросу дает вам результат, который вам нужен для заданных входных данных.

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'
person Lieven Keersmaekers    schedule 29.11.2010

arrow_upward
0
arrow_downward

ты имеешь ввиду:

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'
person Randy    schedule 29.11.2010

arrow_upward
0
arrow_downward

Вам не нужно использовать LEFT JOIN там, где подходит INNER JOIN. Вам нужна колонка на profile_picture, верно? Кажется, все, что вам нужно, это дополнительное условие WHERE:

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

Есть ли возможность иметь более одного столбца в правой таблице, и вы хотите получить только один? Хотите последнее утвержденное изображение? Добавьте это:

GROUP BY
   member_id
ORDER BY
   image_id
person Explosion Pills    schedule 29.11.2010
comment
В результат не будут включены участники с 0 изображениями. - person ksogor; 29.11.2010
comment
Судя по его вопросу, это не так, но это легко решить. Просто измените JOIN на LEFT JOIN и поместите условие image_approved в предложение ON (в настоящее время предложение USING). - person Explosion Pills; 29.11.2010

arrow_upward
0
arrow_downward

Либо приспособьте NULL в предложении WHERE, как рекомендовал Донни, либо используйте табличное выражение, чтобы ограничить вашу вторую таблицу следующим образом:

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
person Lynette Duffy    schedule 29.11.2010