php PDO MySQL Select results of one table based on array of results from another table

Question

The following query will return all "blocker" users and all "blocked" users. Fairly straight forward query.

$stmt=$db->prepare('SELECT blocker,blocked FROM list_blocked 
                    WHERE (blocked = :username AND blocker <> :username) 
                    OR (blocker = :username AND blocked <> :username)');
$stmt->bindParam(':username', $username);
$stmt->execute();

The next query will return all usernames from the members table except the current $username.

$query = $db->prepare("SELECT username FROM members WHERE username <> :username");
$query->bindValue(':user', $username;
$query->execute();
$row = $query->fetchAll();

What I need is a way to get all the values from the results of the first query, excluding the active $username, and then exclude all those results from the second query.

So for example:

Example results of first query:
jim blocked joe
larry blocked joe
steve blocked joe
joe blocked tony
jack blocked joe

Those results applied to the second query would look something like:
$query = $db->prepare("SELECT username FROM members WHERE username <> :username 
                       AND username <> jim 
                       AND username <> larry
                       AND username <> steve
                       AND username <> tony
                       AND username <> jack");

How exactly would I achieve something like this?

UPDATE:

So I am trying to use a subquery but it keeps returning "Operand should contain 1 column(s)"

SELECT username FROM members WHERE username NOT IN (
    SELECT blocker,blocked FROM list_blocked 
                           WHERE (blocked = 'viraladmin' AND blocker <> 'viraladmin') 
                           OR (blocker = 'viraladmin' AND blocked <> 'viraladmin')
 )

ANOTHER UPDATE:

I attempted to use an inner join to achieve this, however the results always return empty, which I assume is because there is actually nothing to join.

SELECT members.username, list_blocked.blocker, list_blocked.blocked
FROM members
INNER JOIN list_blocked 
ON members.username = list_blocked.blocker
AND members.username = list_blocked.blocked
WHERE members.username <>  'username' 
AND (list_blocked.blocked = 'username' AND list_blocked.blocker <> 'username') 
OR (list_blocked.blocker = 'username' AND list_blocked.blocked <> 'username')

Why not use something like a subquery to determine what not to select? — Blake
– Blake, Commented Sep 13, 2016 at 19:08
@Blake Well the answer is, because I have no clue how to do that, thats perhaps what I am hoping to learn. :) — Bruce
– Bruce, Commented Sep 13, 2016 at 19:10
Certainly if someone threw you a google bone, you could do it though. — Blake
– Blake, Commented Sep 13, 2016 at 19:12
When you mix AND and OR in a WHERE clause, you need to be very careful to use parentheses, because AND has higher precedence than OR. — Barmar
– Barmar, Commented Sep 14, 2016 at 16:43

Zack · Accepted Answer · 2016-09-13 21:35:06Z

1

You can accomplish this using a sub query:

SELECT username FROM members WHERE username NOT IN (
SELECT blocker,blocked FROM list_blocked 
                WHERE (blocked = :username AND blocker <> :username) 
                OR (blocker = :username AND blocked <> :username)
 )

Edited - to fit a SQL query you would need to union in the subquery so the results return no more then one column. :

SELECT username FROM members WHERE username NOT IN (
   SELECT blocker as username FROM list_blocked 
                WHERE (blocked = :username AND blocker <> :username) 
                OR (blocker = :username AND blocked <> :username)
   UNION
   SELECT blocked as username FROM list_blocked 
                WHERE (blocked = :username AND blocker <> :username) 
                OR (blocker = :username AND blocked <> :username)
 )

edited Sep 13, 2016 at 21:35

answered Sep 13, 2016 at 19:14

Zack

1271 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Blake Over a year ago

I actually don't think this would work because of the multiple column selection in the sub query.

Bruce Over a year ago

Ironically the first solution didn't work because they removed my second column selection from their query, and this solution doesn't work, because it returns an error.

Zack Over a year ago

Bruce, since you are concerned with only the usernames from the blocking table, try doing a union, which will join the two results as a single column. I think that will work.

Community · Accepted Answer · 2017-05-23 11:51:41Z

0

You can also use the LEFT JOIN - NULL pattern for this.

SELECT username
FROM members AS m
LEFT JOIN list_blocked AS b
ON m.username IN (b.blocker, b.blocked)
    AND ((blocked = :username AND blocker <> :username) 
        OR (blocker = :username AND blocked <> :username))
WHERE b.blocked IS NULL

This is based on the pattern in Return row only if value doesn't exist

edited May 23, 2017 at 11:51

CommunityBot

11 silver badge

answered Sep 14, 2016 at 16:42

Barmar

789k57 gold badges554 silver badges669 bronze badges

Collectives™ on Stack Overflow

php PDO MySQL Select results of one table based on array of results from another table

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related