LEFT JOIN help please

[dragonfly4]

Hi,

I'm trying to do a select of all members that don't have an entry in an other table for a particular ID (warid in my case).
This is for my CounterStrike clan so it has nothing to do with real violence and so on.

This gives me all members that are not in the warteam table:
SELECT eie_member.member_id_pk,eie_member.member_nickname FROM eie_member
LEFT JOIN eie_warteam ON eie_member.member_id_pk = eie_warteam.warteam_playerid_pk_fk
WHERE eie_warteam.warteam_playerid_pk_fk IS NULL

But thats not enough since their are different wars so I wanted to add this to the WHERE clause but then he doesn't find andything anymore. Yes, my warteam table has data with warid = 2:
eie_warteam.warteam_warid_pk_fk = 2

Anyone any ideas how to solve this problem ?
I hope you understand me a bit.

[Lars_Berg]

If you have restrictions on the right table, put them in the ON clause.

i.e.

ON eie_member.member_id_pk = eie_warteam.warteam_playerid_pk_fk and <otherCondition>

[dragonfly4]

thanks alot man, that solved my problem !!!
🙂 😃 😉

For people who would like to know, this is the query at the moment. In the code you would offcourse need to replace 2 by a var:

SELECT eie_member.member_id_pk,eie_member.member_nickname FROM eie_member
LEFT JOIN eie_warteam ON eie_member.member_id_pk = eie_warteam.warteam_playerid_pk_fk AND eie_warteam.warteam_warid_pk_fk = 2
WHERE eie_warteam.warteam_playerid_pk_fk IS NULL