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, Sep 13 '16 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, Sep 13 '16 at 19:10
Certainly if someone threw you a google bone, you could do it though. — Blake, Sep 13 '16 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, Sep 14 '16 at 16:43

Zack · Accepted Answer · 2016-09-13T21:35:06.243

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 '16 at 21:35

answered Sep 13 '16 at 19:14

Zack

129
1
8

1

I actually don't think this would work because of the multiple column selection in the sub query. – Blake Sep 13 '16 at 19:16
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. – Bruce Sep 13 '16 at 19:24
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. – Zack Sep 13 '16 at 21:38

score 0 · Answer 2 · edited May 23 '17 at 11:51

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 '17 at 11:51

Community

1
1

answered Sep 14 '16 at 16:42

Barmar

741,623
53
500
612

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

2 Answers2