I have a table like this in my database:
x
+-----------+-------+--------+--------------+| 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 tgroup by person_id, month, namehaving sum(case when special = 'content' then 1 else 0 end) = 0;