April 10, 2008 at 1:58 pm
Hello All,
I now have the window of time to post a query example that I can really use your expertise on in "breaking it down" piece by piece.
Before we get started, please do not tell me how the query should be written yet, I would like to try to do it myself based on your feedback.
Here it is, and I will give my interpretation of what it requires piece by piece and please let me know if I'm on the right track.
Schema:
frequents: drinker, bar
serves: bar, beer
likes: drinker, beer
Frequents = The first indicates the bars each drinker frequents,
Serves = the second indicates what beers each bar serves,
Likes = the third indicates which beers each drinker likes to drink.
Query:
List the drinkers who do not frequent a bar that serves a beer that they like.
My questions:
1) What is the first piece? Finding the complement in the form of finding a bar that the drinker frequents that DOES serve a beer they like? If so, what is the best way to "find a complement?"
2) If I use a subquery format, do I focus on the second half of the query first and then work "backwards."?
3) In regard to conditions such as "only," "any," "all," "at least one,", etc...how do I translate this criteria into the proper syntax? I am getting confused as to whether it's a double negative or .....?
4) How do I determine which type of query I should use, e.g. "IN," "NOT IN," "EXISTS," etc....
Thanks in advance for your help. I have done several queries very similar to this one, and although I am understanding more and more, these are still very confusing for me to get straight in my head.
-Roberta-
April 10, 2008 at 2:29 pm
first start with telling the system what you know:
i.e. Likes (a drinker likes beer)
then join it with what you know next:
i.e. Serves (you know the bars where they server what he likes
then go for the ones the drinker did not frequent.
i.e. frequents: drinker, bar
The tricky part is the last one because you may want to test different ways to acheve
this:
And because there is a "not" involved, most of the time an "not exists" outperforms (correlated) a "left join" with the extra condition where frequents.drinker is null
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 10, 2008 at 2:50 pm
HI ALZDBA,
Thanks for the quick response.
I want to show you what I have based on what you stated.
SELECT drinker (drinker likes beer)
FROM likes, serves (joins drinker with the bar that serves beer they like
As for the last part of the query, this is where I am confused.
What is the best way to narrow down the information to show no bars. Is it like this:
WHERE bar NOT IN(SELECT bar from serves
(SELECT beer FROM likes WHERE frequents.drinker = likes.drinker)
April 11, 2008 at 12:18 am
Actualy... since you're starting ... maybe better to be using join syntax.
SELECT L.drinker -- (drinker likes beer)
FROM likes L
inner join serves S -- (joins drinker with the bar that serves beer they like
on L.beer = S.beer
As for the last part of the query, this is where I am confused.
What is the best way to narrow down the information to show no bars. Is it like this:
WHERE bar NOT IN(SELECT bar from serves
(SELECT beer FROM likes WHERE frequents.drinker = likes.drinker)
This would go to e.g.
SELECT L.drinker -- (drinker likes beer)
, L.beer -- the beer (s)he likes
, S.bar -- the bar (s)he didn't visit
FROM likes L
inner join serves S -- (joins drinker with the bar that serves beer they like
on L.beer = S.beer
WHERE NOT exists (SELECT *
from frequents F
WHERE F.drinker = L.drinker
and F.bar = S.bar)
another version is
SELECT L.drinker -- (drinker likes beer)
, L.beer -- the beer (s)he likes
, S.bar -- the bar (s)he didn't visit
FROM likes L
inner join serves S -- (joins drinker with the bar that serves beer they like
on L.beer = S.beer
left join frequents F
on F.drinker = L.drinker
and F.bar = S.bar
Where F.drinker is null -- an unmatching richt part of a left join results in nulls for the columns of the righte part.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply