Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
deco10
Starting Member
28 Posts |
 Posted - 2010-12-01 : 18:44:31
|
| I posted this question and then deleted it because my example was terrible.This the query I'm trying to get workingI want to find all of the events that have either a) no people b) no people with the person_category of 2c) a count of the events with no person of type 2SELECT count(*) as cnt, events.id, people.person_category_id, event_people.* FROM events INNER JOIN event_people ON events.id = event_people.event_id INNER JOIN people ON people.id = event_people.person_id GROUP by YEAR(events.start_date), MONTH(events.start_date)HAVING people.person_category_id NOT IN (2) ORDER BY events.idThe following should output something like the following:2010 Jan - count:10month year - number of events with no person of category 2.........2010 Aug - count:3 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
deco10
Starting Member
28 Posts |
Posted - 2010-12-01 : 20:02:36
|
| My applogies, I thought it was discernable from the query. Let me know if more detail is required.In the example below I would want the query to return event with id=2 because that event has no event_people where the person.person_category is equal to 2Here is my table layout:events:id, start_date1, 2005-10-012, 2005-11-043, 2006-04-02people:id, name, birthdate, person_category_id1, Dave, 1950-10-04, 22, Mike, 1958-11-13, 13, Lisa, 1967-07-25, 34, Stef, 1961-01-05, 4event_people:event_id, person_id,1,11,22,22,32,43,13,4 |
 |
|
|
|
|
|
|
|
Couting certain join records