December 6, 2010 at 5:40 pm
I'm slogging my way through this Celko article:
http://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/
First he creates his two tables:
CREATE TABLE PilotSkills
(pilot_name CHAR(15) NOT NULL,
plane_name CHAR(15) NOT NULL,
PRIMARY KEY (pilot_name, plane_name));
/*
PilotSkills
pilot_name plane_name
=========================
'Celko' 'Piper Cub'
'Higgins' 'B-52 Bomber'
'Higgins' 'F-14 Fighter'
'Higgins' 'Piper Cub'
'Jones' 'B-52 Bomber'
'Jones' 'F-14 Fighter'
'Smith' 'B-1 Bomber'
'Smith' 'B-52 Bomber'
'Smith' 'F-14 Fighter'
'Wilson' 'B-1 Bomber'
'Wilson' 'B-52 Bomber'
'Wilson' 'F-14 Fighter'
'Wilson' 'F-17 Fighter' */
CREATE TABLE Hangar
(plane_name CHAR(15) NOT NULL PRIMARY KEY);
/*
Hangar
plane_name
=============
'B-1 Bomber'
'B-52 Bomber'
'F-14 Fighter'
PilotSkills DIVIDED BY Hangar
pilot_name
=============================
'Smith'
'Wilson'
*/
Then, in the "Division With A Remainder" section, he gives two examples of how to find a specific result set (pilots that are certified to fly every type of plane that exists in the hangar).
The SECOND example makes sense to me. I was able to take it apart and figure it all out fairly easily:
SELECT PS1.pilot_name
FROM PilotSkills AS PS1, Hangar AS H1
WHERE PS1.plane_name = H1.plane_name
GROUP BY PS1.pilot_name
HAVING COUNT(PS1.plane_name) = (SELECT COUNT(plane_name) FROM Hangar);
But the FIRST example, which accomplishes the same thing, I can't for the life of me work out exactly how/why it is working:
SELECT DISTINCT pilot_name
FROM PilotSkills AS PS1
WHERE NOT EXISTS
(SELECT *
FROM Hangar
WHERE NOT EXISTS
(SELECT *
FROM PilotSkills AS PS2
WHERE (PS1.pilot_name = PS2.pilot_name)
AND (PS2.plane_name = Hangar.plane_name)));
Would anyone care to take a whack at breaking this example down into pieces and/or plain-language to explain further? I suspect it has something to do with the movie "Inception", but I'm not exactly sure... 🙂
December 6, 2010 at 5:52 pm
autoexcrement (12/6/2010)
Would anyone care to take a whack at breaking this example down into pieces and/or plain-language to explain further? I suspect it has something to do with the movie "Inception", but I'm not exactly sure... 🙂
Interesting article.
The statement in question reads in English as such:
Select me pilots who have no planes in the hangar that they cannot fly. The double negation is what's eating you. Break it down a little more.
Starting with the inside: Select me All the pilots and their skills. Next, select any results in the Hangar that are not in thier skill list. Next, tell me any pilots who do not have planes in the hangar that are not in thier skill lists.
This tells you who can fly everything.
Hope that helped.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 6, 2010 at 5:57 pm
I will take a crack at it...
First get all the pilot names
Now get all the plane names
Now check for each combination of pilot name and plane name is there a record in PilotSkills.
If there are matches in PilotSkills for the current Pilot/Plane combination, you don't select the current plane from Hangar (NOT EXISTS)
If there is no match in PilotSkills for the current Pilot/Plane combination, you do select the current plane from Hangar
If you selected the plane from Hangars , you don't select the current Pilot (NOT EXISTS)
If you didn't select the plane from Hangars , you do select the current Pilot
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);December 6, 2010 at 6:40 pm
First, thanks to the two kind gents who offered explanations. I've read them each a dozen times and, while they make sense on their own, I'm still working to correlate your words directly to the SQL. But I will continue to work at it and hopefully it will just "click" at some point--and hopefully that "click" won't be the sound of something breaking.
Second, just out of curiosity, I wonder what solution you would have come up with on your own if presented with this scenario. As you have surely guessed by now, I would never have devised the "double NOT EXISTS" solution. But would you? Do you consider this solution to be a fairly simple, intuitive one? Or would you have solved the problem another way?
And Third, I still don't see where Leonardo DiCaprio fits into all this... 😉
Thanks again, guys!
December 7, 2010 at 6:25 am
The queries aren't nested in the order you would expect.
SELECT DISTINCT pilot_name
FROM PilotSkills AS PS1
WHERE NOT EXISTS
(SELECT *
FROM Hangar
WHERE NOT EXISTS
(SELECT *
FROM PilotSkills AS PS2
WHERE (PS1.pilot_name = PS2.pilot_name)
AND (PS2.plane_name = Hangar.plane_name)));
If you look at the innermost query:
(SELECT *
FROM PilotSkills AS PS2
WHERE (PS1.pilot_name = PS2.pilot_name)
AND (PS2.plane_name = Hangar.plane_name)))
It's a query of pilot skills, but it refers back to the pilot name in the "PS1" table. "PS1" is the alias given to the PilotSkills table in the outermost query.
This means it will query all skills every pilot has.
It then uses the middle query:
(SELECT *
FROM Hangar
WHERE NOT EXISTS
And finds any planes that aren't in the list of skills per pilot. The NOT EXISTS finds rows that aren't in the list of skills per pilot.
Combining the two inner queries, you now have a list of all the skills each pilot lacks. It has compared a complete list of skills vs a complete list of pilots and found the missing ones.
This is then compared, using NOT EXISTS, to the full list of pilots, to find any who aren't missing any skills.
In summary, instead of the human-logic answer of "what pilot has all skills", this works backwards by finding "what pilot isn't missing any skills". They're the same result, but you have to look at it as a negative to get the query.
DiCaprio was out sick the day this list was needed.
And I'd have built it using an outer apply to find a list of missing skills. Same logic, probably the same execution plan, it's just what I would have thought of first.
- 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
December 9, 2010 at 8:40 pm
Sorry for the late reply, I didn't get the usual email notifying me of a new post.
Thanks, G! I think that did it! I'd love to see your "outer apply remix" if you feel like spending a couple minutes on it.
Coincidentally, I've been reading up today about APPLY (Cross Apply in particular) and it seems fascinating, but it's still a bit foreign and I'm trying to grasp it.
This is one of the most amazing web sites I've ever come across. I hope to be able to contribute one day...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply