CODEWITHSUNDEEP
mysqlsqldatabase
eyurdakul
eyurdakul

Reputation: 912

Mysql fetch rows only contain a row with certain value

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?

Upvotes: 0

Views: 146

Answers (4)

Darshan Mehta
Darshan Mehta

Reputation: 30849

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'
);

Upvotes: 1

TheZozoOwner
TheZozoOwner

Reputation: 532

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

Upvotes: 1

Fahad Anjum
Fahad Anjum

Reputation: 1256

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.

Upvotes: 1

Johan
Johan

Reputation: 929

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

Upvotes: 0

Related Questions