## Need some help with a query

 Author Message niklasoldeback Forum Newbie Group: General Forum Members Points: 2 Visits: 10 I have one final assignment before I can actually start enjoying my summer break. I've been trying to figure it out for the last 3 hours.If any one could give me some pointer I'd highly appreciate it!The assignment is:Find "namn" and "antalplatser" for "Rum" that has been used by all "Lärare" that is from one "ort"The table names are in swedish, but translated the question is: Find rooms that all teachers that were born in LA have held a lecture inMy professor hinted that I should use double negation, but still, I can't figure it out.The relationship diagram:http://imgur.com/CmFpR GSquared SSC Guru Group: General Forum Members Points: 140421 Visits: 9731 The usual way to do that is to find the total count of teachers born in LA, then the count per room of teachers that were born in LA. Where the two counts are equal, you have the right rooms. That's the easy way. - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETCProperty of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon niklasoldeback Forum Newbie Group: General Forum Members Points: 2 Visits: 10 Thank you for your response!I was actually thinking about doing the query like this, the problem is that this is an entry level class, and we haven't been taught how to do that.I found this piece of code that does roughly what I want, do you think I could change this around and get it to work for my query?-- Get part numbers for each part which is-- supplied to all projects in LondonP is Part table, J is Project table, SPJ is supplier part for projectSELECT pnum FROM P WHERE NOT EXISTS ( SELECT * FROM J WHERE city = 'London' AND NOT EXISTS ( SELECT * FROM SPJ WHERE spj.pnum = p.pnum AND spj.jnum= j.jnum)); GSquared SSC Guru Group: General Forum Members Points: 140421 Visits: 9731 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, ETCProperty of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon