Simple query? Or so i thought!

[andyros]

I was looking through some example query questions are found these two:

Consider the following database structure:
pizza(pid, pname, size)
store(sname, phone, quality)
soldby(pid, sname, price)

1. Find the name of all the stores that sell both veggie and cheese pizza.

2. Find the names of all the stores that do not sell veggie pizza.

Can these be done in single queries?

For question 1, surely it involves going through the entire tables to establish weather both pizzas are sold? You cannot do a WHERE clause because the entire set needs to be evaluated and now just the row?

It looked easy at first, then i thought about it and got stumped! Any ideas?

andy

 

[gmmastros]

This can easily be done (when you know how).

However, this feels like a homework assignment. Is it?

I would recommend you do a little research on Count, Group By and Having.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[andyros]

no its not a homework assigment, i know about those three functions, how can they be used? I was thinking about doing something with union, but i think thats way off?

You cant have multiple clauses in HAVING can you?

HAVING pizza.pname = "veggie" AND pizza.pname = "cheese" doesnt seem to work!

 

[gmmastros]

You can have multiple conditions in the having clause. The problem here is that you are using AND. When evaluated for each row, the pizza name cannot be veggie and cheese at the same time. Try OR.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Here. Try this.

Select Soldby.sname
From   Soldby
       Inner Join (
         Select pid
         From   Pizza
         Where  pname in ('veggie', 'cheese')
         Having Count(*) = 2
         ) As P On SoldBy.pid = p.pid

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[andyros]

Using OR will still include stores that sell only one of the two pizzas? not both?

Just built the database and tried that query, it seems to return all the stores that sell either pizza but not both, but this, strangely, seems to work:

SELECT 
  store.sname,pizza.pname 
FROM 
  store,pizza,soldby 
WHERE 
  soldby.sname = store.sname 
AND 
  soldby.pid = pizza.pid 
AND
  pname IN ('veggie', 'cheese')
GROUP BY
  pizza.pname

 

[gmmastros]

This:
pname IN ('veggie', 'cheese')

Is the equivalent of:
pname = 'veggie' or pname = 'cheese'

So, essentially, you are using OR. I may have missed something in my query.

Select Soldby.sname
From   Soldby
       Inner Join (
         Select pid
         From   Pizza
         Where  pname in ('veggie', 'cheese')
         [!]Group By pid[/!]
         Having Count(*) = 2
         ) As P On SoldBy.pid = p.pid

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[PHV]

1)
SELECT B.sname
FROM soldby B
INNER JOIN pizza P ON B.pid=P.pid
WHERE P.pname IN ('veggie','cheese')
GROUP BY B.sname
HAVING COUNT(*)=2

2)
SELECT B.sname
FROM soldby B
LEFT JOIN pizza P ON B.pid=P.pid AND P.pname='veggie'
WHERE P.pid IS NULL


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886