CODEWITHSUNDEEP
mysql
Milkbones
Milkbones

Reputation: 41

Return records from one table where Field not found in another table

I'm so lost here I don't even know how to best title my question.

I am creating a simple dating site. I want the women to be able to block the men just like all other dating sites. When this happens, I don't want the womens' profiles to be returned in a query.

Table A Members table containing all the profile information including a member name Table B Blocked members table containing the woman's name and the man's name for each case in which the woman has blocked a man

So, I want something like this: $query = Return all records from table A where sex=female and there is no record in table B containing the woman's name and the man's name

I thought I would run a query against table B to retrieve all women who have blocked me, then run a query against table A to return all females in which the woman's username is NOT contained in the results of my first query. However, I can't figure out how to do this.

Upvotes: 1

Views: 1334

Answers (3)

user8026
user8026

Reputation: 51

Would this work?

Select * from Table A
inner join Table B on a.womans_name = B.womans_name and B.mans_name="Mans Name"
where B.womans_name IS NULL

If Table B contains a record with the matching womans_name and mans_name then the join will create one record containing all the fields in Table A and Table B but the Where clause will reject this record because the womans_name from Table B will not be null. If Table B does not contain a matching record then all those fields will be null (including B.womans_name) so the Where clause is satisfied.

Upvotes: 0

JonM
JonM

Reputation: 510

If you wanted to see a list of all the female members who had blocked the current user, you would use a query like:

SELECT member.*
FROM TableA member
JOIN TableB blocked ON (member.name = blocked.user_who_blocked)
WHERE member.sex = female
    AND blocked.blocked_user_name = 'Joe McCurrentUser'
;

So, if you want to see the set of users where that is not the case, you use a LEFT JOIN and look for a null id.

SELECT member.*
FROM TableA member
LEFT JOIN TableB blocked ON (member.name = blocked.user_who_blocked)
WHERE member.sex = female
    AND blocked.blocked_user_name = 'Joe McCurrentUser'
    AND blocked.id IS NULL
;

You can modify as you wish to use the actual columns in your tables. Make sure you have indices on both the user_who_blocked and blocked_user_name columns in that table.

Upvotes: 0

KVISH
KVISH

Reputation: 13208

If I understand your question...seems like a simple join, no? Not sure if I'm misunderstanding. Something like this perhaps:

SELECT * FROM Table1 WHERE Table1.ID NOT IN (SELECT BLOCK_ID FROM table2)

So Table1 has all ID's of the women, and Table2 has all block id's (for example) and you want what is not in that? Obviously some changes required on top of this.

Upvotes: 2

Related Questions