CODEWITHSUNDEEP
mysqlsql
Jonathan Bishop
Jonathan Bishop

Reputation: 167

SQL querying to get every possible link

I have a scheme with three tables with the following structure

donuts (name: string, price: integer) 
grocery (no: string, gname: string, credit: integer) 
distributor (dname: string, gno: string, deliverydate: date)

distributor.dname and distributor.gno are foreign keys that reference donuts and grocery which keys are name and no respectivey I am having trouble with 2 queries I am trying to write:

  1. I am trying to query the names of all donuts who are in every "Vons" or "Smiths" grocery store. (specified in gname)

I tried 

SELECT d.name
FROM donuts d, grocery g, distributor dd
WHERE d.name = dd.cname
       AND dd.gno = g.no
       AND g.gname = 'Vons' OR g.gname = 'Smiths'
  1. I am trying to query grocery number (no in grocery) shared by atleast two different donuts.

I tried

SELECT g.no
FROM donuts d, grocery g, distributor dd
WHERE g.no = dd.gno

This doesn't seem to return every occurence or account for OR. How could I fix my queries? I am a little new to sql please pardon my lack of knowledge.

Upvotes: 1

Views: 65

Answers (4)

Thorsten Kettner
Thorsten Kettner

Reputation: 94913

Donuts sold in both Vons and Smiths

There is no need to select from the donuts table, because all we need from it is donauts names, which we also find in distributor.

So we select from grocery to get the store numbers and use these to find dnames in distributor. We group by donut name and simply count their stores.

select dname
from distributor
where gno in (select no from grocery where gname in ('Vons', 'Smiths'))
group by dname
having count(distinct gno) = 2;

In case dname + gno is unique in distributor you can replace count(distinct gno) with count(*).

Stores selling at least two different donuts

Your second query translates to 

select g.no
from donuts d
cross join grocery g
inner join distributor dd on dd.gno = g.no

with proper joins. So you combine all donuts with all groceries first. Then you multiply these with all donuts distributed in the stores.

What you really want is

select gno
from distributor
group by gno
having count(distinct dname) > 1;

Again no need to query any other table; all information needed is in distributor.

Upvotes: 0

Nathan Griffiths
Nathan Griffiths

Reputation: 12756

1) You are using a combination of AND and OR in your query and you need to use brackets to specify the order of operations, otherwise the OR clause will not be interpreted in the way you are intending. Example using brackets:

SELECT d.name
FROM donuts d, grocery g, distributor dd
WHERE d.name = dd.cname
       AND dd.gno = g.no
       AND ( g.gname = 'Vons' OR g.gname = 'Smiths' )

In the example above, the brackets ensure that the OR operation is only between the two gname values, and not between gname = 'Smiths' and the rest of the where clause.

2) Assuming the data only has one instance of each grocery - distributor - donut relationship, and each grocery only gets each donut type from one distributor, you should be able to count the rows and apply a HAVING clause to find where there are two or more relationships:

SELECT g.no, COUNT(*) as Quantity
FROM distributor dd, grocery g, donuts d
WHERE dd.dname = d.name
AND dd.gno = g.no
GROUP BY g.no
HAVING COUNT(*) >= 2

Upvotes: 1

Alex
Alex

Reputation: 17289

My guess you just need JOIN tables properly:

SELECT DISTINCT d.name
FROM grocery g
LEFT JOIN distributor dd
ON dd.gno = g.no
LEFT JOIN donuts d 
ON d.name = dd.cname
WHERE g.gname = 'Vons' OR g.gname = 'Smiths'

Upvotes: 0

Paul Stanley
Paul Stanley

Reputation: 4098

Try cooping up the last or in brackets like this, it might be just trying to look for your key joining with vons, then smiths without the key joins.

SELECT d.name
FROM donuts d, grocery g, distributor dd
WHERE d.name = dd.cname
       AND dd.gno = g.no
       AND (g.gname = 'Vons' OR g.gname = 'Smiths')

Upvotes: 1

Related Questions