mercredi 4 novembre 2015

SQL query to select the same field from multiple tables

I have three tables defined as this in the schema

donuts (name: string, price: integer) 
grocery (no: string, gname: string, minCredit: 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:

I am trying to display pairs of donuts which can be found at the same grocery store (i.e. all stores who carry one donuts are taken by the other and viceversa). The output schema should be (donutname1, donutname2)

What I have tried is:

select d.name as donutname1, d.name as donutname2
from donuts d, grocery g, distributor dd
where d.name IN  
    (select  d.name 
     from donuts d, grocery g, distributor dd 
     where d.name = dd.name)

This seems to wrong and seems to be giving me the wrong result. How could I query for both names and get the result?

Aucun commentaire:

Enregistrer un commentaire