CODEWITHSUNDEEP
mysqlsql
thedirktastik
thedirktastik

Reputation: 23

SQL finding members who are not friends of another member

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?

Upvotes: 0

Views: 161

Answers (3)

Hip Hip Array
Hip Hip Array

Reputation: 4773

what if you try

SELECT Mi.First_Name, Mi.Last_Name
  FROM Member_Info Mi
 WHERE Mi.Memberid NOT IN(
                           SELECT Mi.Memberid
                              FROM Member_Info Mi, Member_Login Ml, Member_Friends 
                            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
                          );

because you are returning three values in your NOT IN ( ... ) when it should be one 

SELECT Mi.Memberid, Mi.First_Name, Mi.Last_Name

should be

SELECT Mi.Memberid

you might get another error about your FROM statement as you will need to join to tables together to compare values but your friend table does not have any matching values so this could be a problem (poor design)

EDIT: database designs

A lot of this comes down to experience really but there are a few good books which you can check out like Head First SQL and Relational Database Design Clearly Explained (The Morgan Kaufmann Series in Data Management Systems) (Paperback), these are great books to learn the basics and basic design, i recommend you check them out

Upvotes: 0

Yaroslav
Yaroslav

Reputation: 6554

@Thedirktastik, please check the correct use of IN clause. Here in the MSDN you can take a look.

In you WHERE clause you are using IN so the subquery returning the values should return only Mi.Memberid column values. And your query is returning several different columns. Should be something like:

....
WHERE Mi.Memberid NOT IN(
                           SELECT Mi.Memberid
                             FROM....

Upvotes: 1

northpole
northpole

Reputation: 10346

This is a really bad design, and probably the reason for the down votes. I would look into making the member_friends table an intersect table (junction table). It will make your design, and therefore queries, much easier to use and understand.

Upvotes: 0

Related Questions