Query if a value does not exist in a table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Alpal
Forum Commoner
Posts: 39
Joined: Mon Jul 26, 2010 4:08 am

Query if a value does not exist in a table

Post by Alpal »

i run a small sporting association website, am querying 2 tables no problem:

SELECT scrs.scrsID, scrs.MemberID, scrs.team_grade, scrs.team_id, scrs.played_S1
FROM scrs, members
WHERE scrs.MemberID = members.MemberID
AND scrs.team_id =32
GROUP BY members.MemberID
ORDER BY members.MemberID

I have a 3rd table that holds members financial information - the table relationships are members.memberID, scrs.memberID and the 3rd table is members_fin.Fin_ID

What I would like to do is find out if memberID exists in the members_fin table (members_fin.Fin_ID) then the member is financial. If the memberID does not exist in the members_fin table (members_fin.Fin_ID) then the member is unfinancial.

I need to report both financial and unfinancial members on the same page. Have tried CASE and If statements but could not get them to work.

As always thanks in advance for any assistance you may offer
pbs
Forum Contributor
Posts: 230
Joined: Fri Nov 07, 2008 5:31 am
Location: Nashik, India
Contact:

Re: Query if a value does not exist in a table

Post by pbs »

Use LEFT JOIN members table with members_fin table, it will give results from members_fin if it exists in members_fin else it will give NULL value
Alpal
Forum Commoner
Posts: 39
Joined: Mon Jul 26, 2010 4:08 am

Re: Query if a value does not exist in a table

Post by Alpal »

Thank you

Have this query now and it reports correctly

SELECT scrs.scrsID, scrs.MemberID, scrs.team_grade, scrs.team_id, scrs.played_S1, members_fin.Fin_ID AS financial
FROM scrs, members
LEFT JOIN members_fin ON members.MemberID = members_fin.Fin_ID
WHERE scrs.MemberID = members.MemberID
AND scrs.team_id =32
GROUP BY members.MemberID
ORDER BY members.MemberID

Really appreciate your assistance
Post Reply