CODEWITHSUNDEEP
mysqlsql
Raold
Raold

Reputation: 1493

SQL - select where condition is true but delete if there is another row that breaks condition

I searched hours on the internet but I found nothing that would help me. Here is my problem...

My table

/ id / number / idobject /
/ 1  /   50   /    2     /
/ 2  /   60   /    2     /
/ 3  /   70   /    2     /
/ 4  /   80   /    1     /
/ 5  /   10   /    2     /
/ 6  /   20   /    1     /
/ 7  /   90   /    3     /

SQL

SELECT * 
FROM table 
WHERE number > 50

Result i want

/ id / number / idobject /
/ 7  /   90   /    3     /

I want only idobjects with at least 1 idobject true(>=50) and no idobject false

Upvotes: 3

Views: 984

Answers (2)

Mihai
Mihai

Reputation: 26784

If i get it,you want idojects with at least 1 idobject true(>=50) and no idobject false

SELECT MAX(id) as id ,MAX(number) as number,idobject 
FROM t
GROUP BY idobject
HAVING SUM(number <50)=0
AND SUM(number >=50)>0

EDIT

if you have more than 1 idobject true,you can replace MAX with GROUP_CONCAT

Upvotes: 3

I_am_Batman
I_am_Batman

Reputation: 915

You could use a NOT IN clause.

select idobject from table where idobject not in(select idobject from table where number<50);

Upvotes: 2

Related Questions