I have a table like this in my database:
+-----------+-------+--------+--------------+ | Person_ID | Month | Name | Special | +-----------+-------+--------+--------------+ | 807 | 01 | Smith | othercontent | | 807 | 01 | Smith | othercontent | | 807 | 02 | Smith | othercontent | | 807 | 02 | Smith | othercontent | | 807 | 02 | Smith | content | | 807 | 02 | Smith | othercontent | | 807 | 03 | Smith | othercontent | | 807 | 03 | Smith | othercontent | | 807 | 03 | Smith | othercontent | | 807 | 04 | Smith | othercontent | | 807 | 04 | Smith | content | | 807 | 04 | Smith | othercontent | | 981 | 01 | Walker | othercontent | | 981 | 01 | Walker | othercontent | | 981 | 02 | Walker | othercontent | | 981 | 02 | Walker | othercontent | | 981 | 02 | Walker | content | | 981 | 02 | Walker | othercontent | | 981 | 03 | Walker | othercontent | | 981 | 03 | Walker | othercontent | | 981 | 03 | Walker | othercontent | | 981 | 04 | Walker | othercontent | | 981 | 04 | Walker | content | | 981 | 04 | Walker | othercontent | +-----------+-------+--------+--------------+
I have to write a SQL-Query that selects every month that does not contain “content” at least once. And it should only give out one line.
So the output should be like this:
+-----------+-------+--------+--------------+ | Person_ID | Month | Name | Special | +-----------+-------+--------+--------------+ | 807 | 01 | Smith | othercontent | | 807 | 03 | Smith | othercontent | | 981 | 01 | Walker | othercontent | | 981 | 03 | Walker | othercontent | +-----------+-------+--------+--------------+
How can I accomplish that with a SQL-Query? I tried to do that with the group by function, but could not get it working properly.
Advertisement
Answer
If you want only one row per person/month combination, then use aggregation. You have use a having clause to filter out the 'content' rows:
select person_id, month, name, max(special) from t group by person_id, month, name having sum(case when special = 'content' then 1 else 0 end) = 0;