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.

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

share|improve this question
    
Which RDBMS are you asking about? –  StanleyJohns Sep 15 '13 at 21:21
    
I'm working on MySQL –  Yaroslav Nychka Sep 16 '13 at 6:26
add comment

1 Answer 1

up vote 1 down vote accepted
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"
      ) ;
share|improve this answer
    
Can you comment your query? What is SELECT 1? What does it do? Thank you. –  Yaroslav Nychka Sep 16 '13 at 6:30
    
SELECT * or SELECT 1 are equivalent in EXISTS and NOT EXISTS subqueries. They mean "one row" –  ypercube Sep 16 '13 at 7:48
    
yeah, It really works! Thanks a lot! –  Yaroslav Nychka Sep 16 '13 at 8:41
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.