Page 1 of 1

Query if a value does not exist in a table

Posted: Fri Jun 21, 2013 3:39 am
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

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

Posted: Fri Jun 21, 2013 5:32 am
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

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

Posted: Fri Jun 21, 2013 5:56 pm
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