How to select/count rows where the same value exists in a column but not in other column all at the same table

Question

I have this table structure and data:

CREATE TABLE IF NOT EXISTS `default_relations_users` (
  `id_user_rq` int(11) NOT NULL,
  `id_user_ap` int(11) NOT NULL,
  UNIQUE KEY `rusers_rq_ap_idx` (`id_user_rq`,`id_user_ap`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `default_relations_users` (`id_user_rq`, `id_user_ap`) VALUES
(1, 2),
(1, 3),
(1, 4),
(2, 1),
(2, 2),
(2, 3);

I need to get only the rows where the value is on the left but not on the right, meaning for example: 1,2 and 2,1 will not be selected but 1,3 and 1,4 yes. I try this query:

SELECT * FROM default_relations_users
WHERE default_relations_users.id_user_rq = 1 
 AND NOT EXISTS (SELECT id_user_ap FROM default_relations_users WHERE id_user_ap = 1) 

Maybe I don't understand what Subqueries mean or I'm using in the wrong way. So any help?

Answer 1

SELECT A.*
FROM default_relations_users A
LEFT JOIN default_relations_users B
ON  A.id_user_rq = B.id_user_ap
AND A.id_user_ap = B.id_user_rq
WHERE B.id_user_rq IS NULL;

I loaded you sample data (I added an additional index)

mysql> use Reynierpm
Database changed
mysql> DROP TABLE IF EXISTS `default_relations_users`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `default_relations_users` (
    ->   `id_user_rq` int(11) NOT NULL,
    ->   `id_user_ap` int(11) NOT NULL,
    ->   UNIQUE KEY `rusers_rq_ap_idx` (`id_user_rq`,`id_user_ap`),
    ->   UNIQUE KEY `rusers_ap_eq_idx` (`id_user_ap`,`id_user_rq`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO `default_relations_users` (`id_user_rq`, `id_user_ap`) VALUES
    -> (1, 2), (1, 3), (1, 4), (2, 1), (2, 2), (2, 3);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from default_relations_users;
+------------+------------+
| id_user_rq | id_user_ap |
+------------+------------+
|          1 |          2 |
|          1 |          3 |
|          1 |          4 |
|          2 |          1 |
|          2 |          2 |
|          2 |          3 |
+------------+------------+
6 rows in set (0.00 sec)

mysql>

Here is the result of my answer

mysql> SELECT A.*
    -> FROM default_relations_users A
    -> LEFT JOIN default_relations_users B
    -> ON  A.id_user_rq = B.id_user_ap
    -> AND A.id_user_ap = B.id_user_rq
    -> WHERE B.id_user_rq IS NULL;
+------------+------------+
| id_user_rq | id_user_ap |
+------------+------------+
|          1 |          3 |
|          1 |          4 |
|          2 |          3 |
+------------+------------+
3 rows in set (0.00 sec)

mysql>

Give it a Try !!!

mysql> SELECT A.*
    -> FROM default_relations_users A
    -> LEFT JOIN default_relations_users B
    -> ON  A.id_user_rq = B.id_user_ap
    -> AND A.id_user_ap = B.id_user_rq
    -> WHERE B.id_user_rq IS NULL
    -> AND A.id_user_rq = 1;
+------------+------------+
| id_user_rq | id_user_ap |
+------------+------------+
|          1 |          3 |
|          1 |          4 |
+------------+------------+
2 rows in set (0.01 sec)

mysql>

Answer 2

Rolando's answer is working fine. The logic of LEFT JOIN is sometimes hard to follow and I find NOT EXISTS much easier to write and understand, so here's another solution, that tries to not change your attempt much:

SELECT * 
FROM default_relations_users AS a             --- alias added here
WHERE id_user_rq = 1 
  AND NOT EXISTS 
      ( SELECT *                              --- no column is needed in EXISTS
                                              --- subqueries
        FROM default_relations_users AS b     --- alias again
        WHERE b.id_user_ap = 1
          AND b.id_user_rq = a.id_user_ap     --- this line added to identify 
                                              --- the two related rows that you
                                              --- want to exclude 
      ) 

The subquery is made a "correlated subquery" with this change.

Edit By RolandoMySQLDBA 2012-05-17 16:30

I tested your query against the same sample data

mysql> SELECT *
    -> FROM default_relations_users AS a
    -> WHERE id_user_rq = 1
    ->   AND NOT EXISTS
    ->       ( SELECT *
    ->         FROM default_relations_users AS b
    ->         WHERE b.id_user_ap = 1
    ->           AND b.id_user_rq = a.id_user_ap
    ->       );
+------------+------------+
| id_user_rq | id_user_ap |
+------------+------------+
|          1 |          3 |
|          1 |          4 |
+------------+------------+
2 rows in set (0.00 sec)

mysql>

Works fine. +1 !!!

Answer 3

what about this? not certain that your question is understood.

based on your revised description/ comment , i have rewritten the code... let me know your thoughts:

select a.* 
from 
default_relations_users a left outer join 
default_relations_users b on (a.id_user_rq=b.id_user_ap and a.id_user_ap=b.id_user_rq)
where b.id_user_rq is null