CODEWITHSUNDEEP
mysql
user966939
user966939

Reputation: 726

Excluding rows with related values

Let's say I have a table that looks like this:

++++++++++++++++++++++++++++++++++++++++
  url        | fieldname    | value
++++++++++++++++++++++++++++++++++++++++
  a,b,c      | RATE         | 45
----------------------------------------
  a,b,c      | PLAY         | 5
----------------------------------------
  a,b,c      | DATE         | 2013-05-20
----------------------------------------
  d,e,f      | PLAY         | 2
----------------------------------------
  d,e,f      | DATE         | 2013-01-01
++++++++++++++++++++++++++++++++++++++++

How can I exclude ALL rows with one particular (unknown!) value in the url column, IF one of the rows with the same url value has a particular (known!) value in the fieldname column?

As an example, how would I do if I wanted to exclude rows with RATE in fieldname, but also the "related" rows (those with the same value in the url column) -- so that it would return a table like this?:

++++++++++++++++++++++++++++++++++++++++
  url        | fieldname    | value
++++++++++++++++++++++++++++++++++++++++
  d,e,f      | PLAY         | 2
----------------------------------------
  d,e,f      | DATE         | 2013-01-01
++++++++++++++++++++++++++++++++++++++++

Again, keep in mind that the url value should not have to be specified in the query.

Upvotes: 0

Views: 61

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271013

This is an example of a "sets-within-sets" query. I like using aggregation with the having clause, because it is the most general approach.

The following query gets the URLs you are interested in:

select url
from t
group by url
having sum(case when fieldname = 'RATE' then 1 else 0 end) = 0;

To get all the rows in the original table, you need to join back to it:

select t.*
from t join
     (select url
      from t
      group by url
      having sum(case when fieldname = 'RATE' then 1 else 0 end) = 0
     ) turl
     on t.url = turl.url

Upvotes: 1

Related Questions