Your innermost query will be a list of all the teachers from LA.
Assuming a standard many-to-many structure, where you have a table-in-the-middle that has teachers and rooms in it, you then need a list of all rooms that don't have a connection to any one of those teachers.
So, yeah, nested Not Exists is probably what your teacher is looking for. He did mention "double-negative" to you.
The problem with that query, unless the specification is suffering a bit, is that if you have a room where all the teachers from LA have lectured, but it has also had teachers from Prague, the query won't return that room. At least, it looks like it won't, to me. You'll need to test it to make sure.
If it were my homework (which would also mean it would be something like 1987 and SQL Server wouldn't exist yet, but we'll ignore that), I'd start out by creating a simple Rooms table, with two or three rows, a Teachers table with at least 3 rows (2 from LA, 1 from wherever else strikes your fancy), and a Lectures table that joins the two. Then try modifying the query you found to fit the tables. Cities becomes rooms, projects becomes lectures, parts is teachers. See if you can make it fit.
Then try modifying the Lectures data to fit different combinations. What if all teachers have held lectures in all rooms? What if there isn't a room that fits the bill (maybe one teacher from LA always lectures in Room 1 while the other always lectures in Room 2)? What if all teachers use Room 1 (like my Prague example above)? What if ... figure out a few other permutations, get creative, and see if you can break the query various ways.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon