Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers 3

up vote 3 down vote accepted
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>
share|improve this answer
    
ok this returns 1,3 - 1,4 - 2,3 but if I want only where id_user_rq = 1? –  ReynierPM May 17 '12 at 19:18
    
nice job +1. @ReynierPM then add the AND a.id_user_rq = 1 to the where clause –  Derek Downey May 17 '12 at 19:19
    
I updated my answer from your question in the comment –  RolandoMySQLDBA May 17 '12 at 19:20
    
@RolandoMySQLDBA boooo, you did it faster than me! hehe good one +1 –  jcho360 May 17 '12 at 19:20
    
@RolandoMySQLDBA niceeeeeee works like a charm, many thanks +1000 not +1 :) –  ReynierPM May 17 '12 at 19:22
add comment

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 !!!

share|improve this answer
add comment

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
share|improve this answer
    
Ok, see the example data, right? I need to get the values from id_user_ap where id_user_rq = 1 but where id_user_ap != 1. In the data I leave here as example 1,2/2,1 will not be returned but 1,3 and 1,4 will be returned –  ReynierPM May 17 '12 at 19:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.