raold raold - 7 months ago 9
SQL Question

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

Answer

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