CODEWITHSUNDEEP
phpmysqlsql
Arsom Nolasco
Arsom Nolasco

Reputation: 105

How to compare two selects?

I have two tables:

enter image description here

  $email = a@a
  SELECT * FROM users WHERE email<>'$email' AND channel <> '( SELECT * FROM inscricoes WHERE email ='$email')' ORDER BY RAND();

How to get this result? 

d@d    |    ddddd

Upvotes: 0

Views: 54

Answers (2)

spencer7593
spencer7593

Reputation: 108510

I would use an anti-join pattern 

SELECT u.email
     , u.channel 
  FROM users u

    -- anti-join, exclude rows with matching channel
  LEFT
  JOIN inscricoes i
    ON i.email = 'a@a' 
   AND i.channel = u.channel
 WHERE i.email IS NULL 

   AND u.email <> 'a@a'
 ORDER BY u.channel

That says, get all rows from users (except for the row for user = 'a@a')

And along with those rows, get any matching rows from inscricoes.

The twist is the condition in the WHERE clause.

We are guaranteed that any rows that had a match will have a non-NULL value for i.channel (since a NULL value would not satisfy the equality condition in the ON clause). So if we exclude rows that have a non-NULL value in i.channel, we are left with rows that didn't have a match.

An equivalent result can be obtained using a NOT EXISTS (correlated subquery) pattern

SELECT u.email
     , u.channel 
  FROM users u
 WHERE u.email <> 'a@a'
   AND NOT EXISTS 
       ( SELECT 1 
           FROM inscricoes i
          WHERE i.email = 'a@a' 
            AND i.channel = u.channel
       )
 ORDER BY u.channel

Upvotes: 3

stevendesu
stevendesu

Reputation: 16841

I'm not sure who taught you the ORDER BY RAND() trick, but I strongly advise forgetting it forever. Unless you absolutely need the results in a random order, this is just slowing down your queries for no reason.

That said, given the data you provided you can do something like this:

SELECT
    users.email as email,
    users.channel as channel
FROM
    users
LEFT JOIN
    inscricoes ON users.channel = inscricoes.channel
WHERE
    users.email <> '$email'
    AND
    inscricoes.email <> '$email'

This will create a temporary table:

+-------------+---------------+------------------+--------------------+
| users.email | users.channel | inscricoes.email | inscricoes.channel |
+-------------+---------------+------------------+--------------------+
| a@a         | aaaaa         | NULL             | NULL               |
| b@b         | bbbbb         | a@a              | bbbbb              |
| c@c         | ccccc         | a@a              | ccccc              |
| d@d         | ddddd         | NULL             | NULL               |
+-------------+---------------+------------------+--------------------+

Then it runs the query on this temporary table. However if you have multiple values in the inscricoes table for a single channel, this could lead to duplicate results. If that's the case, let us know and I'll work on a better query for you.

Edit

Based on your comment, I think I have a better idea of what you're trying to accomplish. Give this query a try:

SELECT
    distinct(channel)
FROM
    users
WHERE
    email <> '$email'
    AND
    channel NOT IN (
        SELECT channel FROM inscricoes WHERE email = '$email'
    )

This will:

  1. Get a list of channels that a@a is subscribed to (the subquery)
  2. Get a list of all channels excluding a@a's channel
  3. Filter the first list from the second list
  4. Return all results (ignoring duplicates)

Upvotes: 2

Related Questions