Lets say, my tables has the following rows:
id | prop1 | prop2
______________________
1 foo a
2 bar a
3 foo b
4 bar b
5 foo c
Now in this case, i want to get rows grouped by prop1 but ignore the groups that don't contain a row with prop2=c
So the intended result is:
1 foo a
3 foo b
5 foo c
Note, now there is only one group in the example but i want to be able to get all of them. How can i achieve this approach?
You can use exists clause to remove all the rows if it does not have prop2 = c using below query.
select *
from your_table t1
where
exists (select 1 from your_table t2 where t1.prop1 = t2.prop1
and t2.prop2 = 'c')
Explaination: Exists caluse will return true if it finds c for that group else false.
SELECT * FROM table WHERE prop2 != "c" GROUP BY prop1
This line is gonna delete att rows with C and the group everything else on prop1
Your expected result is not grouped by prop1. If it was grouped by prop1 then you would get only one record in the result.
To achieve the above, we can write a simple SELECT with sub-query, e.g.:
SELECT *
FROM table
WHERE prop1 IN (
SELECT prop1 FROM table WHERE prop2 = 'c'
);
group by, the rows are listed according to prop1
distinct so it should be better now. Also, having an index on prop2 will speed it up..The following query fetches all record with prop2 = 'c' (T1) and joins them on all records (T2) that have an equal prop1:
select T2.*
from TABLE T1
join TABLE T2 ON T1.prop1=T2.prop1
WHERE T1.prop2 = 'c'
GROUP BY id
prop1can have differentprop2's, which one you want to show?