SQL finding members who are not friends of another member
- by thedirktastik
I'm struggling to find a query which will return members who aren't friends of a certain member. Here is the layout of my tables:
member_login:
MemberID, email, password
member_info:
memberID, first_name, last_name
member_friends:
friendID, req_member, req_date, app_member, app_date, date_deactivated
I tried to use NOT IN to run a query that would return the opposite of friends but nothing I try seems to be working. Here's what I thought would work:
SELECT Mi.First_Name, Mi.Last_Name
FROM Member_Info Mi
WHERE Mi.Memberid NOT IN(
SELECT Mi.Memberid, Mi.First_Name, Mi.Last_Name
FROM Member_Info Mi, Member_Login Ml, Member_Friends Mf
WHERE Mi.Memberid = Ml.Memberid
AND (Mi.Memberid = Mf.Req_Member
AND Mf.App_Member = 1
OR Mi.Memberid = Mf.App_Member
AND Mf.Req_Member =1)
AND Ml.Date_Deactivated <= 0
AND Mf.App_Date > 0
);
Any ideas?
