SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need some help with a query


Need some help with a query

Author
Message
niklasoldeback
niklasoldeback
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

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 in

My professor hinted that I should use double negation, but still, I can't figure it out.

The relationship diagram:
http://imgur.com/CmFpR
GSquared
GSquared
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58503 Visits: 9730
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, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
niklasoldeback
niklasoldeback
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

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 London
P is Part table, J is Project table, SPJ is supplier part for project
SELECT 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
GSquared
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58503 Visits: 9730
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search