0

+----+----------------------+---------+---------+---------------------+-------+
| id | translation          | id_word | id_user | added               | tuser |
+----+----------------------+---------+---------+---------------------+-------+
| 17 | допомагати           |       4 |       1 | 2013-08-29 14:52:20 |     2 |
| 17 | допомагати           |       4 |       1 | 2013-08-29 14:52:20 |     1 |
|  5 | когось               |       1 |       1 | 2013-08-27 23:35:09 |     1 |
|  4 | хто-небудь           |       1 |       1 | 2013-08-27 23:35:09 |  NULL |
|  1 | хтось                |       1 |       1 | 2013-08-27 23:34:17 |     2 |
|  1 | хтось                |       1 |       1 | 2013-08-27 23:34:17 |     1 |
+----+----------------------+---------+---------+---------------------+-------+

As you can see, I have duplicate entries in id. I have to avoid duplicate entries if:

There is a row with a tuser = 1, then I have to remove other entries with the same id, id_user and id_word from result. If in rows (with id, id_user and id_word) no tuser = 1, it must show all values.

I need only one unique row if tuser = 1 for each unique combination id, id_word, id_user

2
  • Which RDBMS are you asking about? Sep 15, 2013 at 21:21
  • I'm working on MySQL Sep 16, 2013 at 6:26

1 Answer 1

1
SELECT a.*                                -- select all rows
FROM tableX  a                            -- from table
WHERE a.tuser = 1                         -- where either tuser = 1
   OR NOT EXISTS                          -- or there is no other 
      ( SELECT *                          -- row 
        FROM tableX  b                    -- in the table
        WHERE b.tuser = 1                 -- with tuser = 1
          AND b.id = a.id                 -- and same "id",
          AND b.id_user = a.id_user       -- same "id_user"
          AND b.id_word = a.id_word       -- and same "id_word"
      ) ;
2
  • Can you comment your query? What is SELECT 1? What does it do? Thank you. Sep 16, 2013 at 6:30
  • SELECT * or SELECT 1 are equivalent in EXISTS and NOT EXISTS subqueries. They mean "one row" Sep 16, 2013 at 7:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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