CODEWITHSUNDEEP
mysqlsql
Imran Azad
Imran Azad

Reputation: 1404

MySQL query on rows, only return row if row item does not exist in other rows

     ID      stone_free    original_stone_id
   ------- | ---------- | -------------------
     1     |     0      |         1
     2     |     1      |         2
     3     |     1      |         1

I would like to return rows from the table only if stone_free equals 0 and there is no corresponding original_stone_id in other rows for each row. So for example in the above example row 1 has stone_free as 0 and there is a corresponding original_stone_id in row 3, therefore the query shouldn't return any rows.

In the example below row 1 has stone_free as 0 but there is no corresponding original_stone_id in the other row, therefore the query should return row 1. 

     ID      stone_free    original_stone_id
   ------- | ---------- |  -------------------
     1     |     0      |         1
     2     |     1      |         2

Upvotes: 0

Views: 359

Answers (3)

Attila Szasz
Attila Szasz

Reputation: 3073

Select * from tablename t1
Where stone_free = 0
And Not Exists (Select Id from tablename t2 where t2.original_stone_id = t1.original_stone_id And t2.Id <> t1.Id)

Upvotes: 1

Joachim Isaksson
Joachim Isaksson

Reputation: 180927

A simple LEFT JOIN should do it;

SELECT a.* 
FROM Stones a
LEFT JOIN Stones b ON a.ID<>b.ID AND a.original_stone_id = b.original_stone_id
WHERE b.ID IS NULL AND a.stone_free=0

Demo here.

Upvotes: 2

yogi
yogi

Reputation: 19591

I'm not sure if it'll work on MySQL but give a shot to it

select  * 
from tbl
where   stone_free = 0 and
     ( 
    select count(tab.original_stone_id) 
    from tbl tab 
    where tab.original_stone_id = tbl.original_stone_id
     ) = 1

Upvotes: 1

Related Questions