MySQL显示了尚未选择的成员

Question

We offer some bonuses for members to choose from and I want to show a popup with the bonuses they have not already selected. bonus contains info on the different bonuses, and selected_bonuses contains bonus id and the members id.

SELECT bonus.* FROM bonus 
LEFT JOIN selected_bonuses 
ON bonus.id = selected_bonuses.bonus_id 
WHERE selected_bonuses.member_id = 'xxxx'

This will show the bonuses selected for member 'xxxx'.

SELECT bonus.* FROM bonus 
LEFT JOIN selected_bonuses 
ON bonus.id = selected_bonuses.bonus_id 
WHERE selected_bonuses.member_id IS NULL

This show the bonuses not selected by any member. I want to show what bonuses just member 'xxxx' have not chosen.

Any suggestions?

Answer 1

With your query

SELECT bonus.* FROM bonus 
LEFT JOIN selected_bonuses 
ON bonus.id = selected_bonuses.bonus_id 
WHERE selected_bonuses.member_id = 'xxxx'

you join information about each bonus to each member having selected that bonus, then select only one member.

If you instead join each bonus to just the member you are interested in, and then select the bonuses for which no such member exists, you should get what you want:

SELECT bonus.* FROM bonus 
LEFT JOIN selected_bonuses 
ON bonus.id = selected_bonuses.bonus_id 
AND selected_bonuses.member_id = 'xxxx'
WHERE selected_bonuses.member_id IS NULL