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
Query if a value does not exist in a table
Moderator: General Moderators
Re: Query if a value does not exist in a table
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
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
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