How to "JOIN" in SQL

sample queries


SELECT *
FROM tblmeals CROSS JOIN tblpartof


SELECT companyname, i.venodorid, i.name
FROM tblvendors v FULL JOIN tblingredients i ON v.vendorid =i.venodrid


SELECT i1.name
FROM tblitems i1 INNER JOIN items i2 ON i1.price > i2.price
WHERE i2.name ='garden Salad';


SELECT name
FROM tblingredients i INNER JOIN vendors v ON i.vendorid = v.vendorid
WHERE v.companyname ='Veggries_R_Us';


SELECt i1name,COUNT(*) AS rank
FROM tblingredient i1.tblingredients i2
WHERE i1.name >= i2.name
GROUP BY i1.ingredientid, i1.name
ORDER BY rank

SELECT DISTINCT (i.name)
FROM tblitems a, tb;litems q,tblmadewith m, tblingredients i
WHERE a.price > q.price AND q.name 'Garden salad' AND i.ingredientid AND m.itemid = a.itemid;




SELECT items.name, ing.name
FROM tblitems,tblmadewith mw, tblingredients ing
WHERE item.itemid =mw.itemid AND mw.ingredientid =ing.ingredientid AND 3 * mw.quantity > ing.inventory;


SELECT m.name as meal, i.name as item
FROM tblmeals m, tblpartof p, tblitems i,
WHERE i.itemid = p.itemid;


SELECT *
FROM tblmeals mi, tblmeals m2;


SELECT m.name AS meal, i.name as item
FROM tblmeals m, tblpartof p, tblitems i
WHERE m.mealid = p.mealid AND i.itemid = p.itemid


SELECT *
FROM tblmeals, tblpartof;


SELECT ingredientid
FROM tblingredients , vendors
WHERE name =companyname


SELECT i.name
FROM tblingredients i, tblmadewith mw
WHERE i.ingredientid = mw.ingredientid AND inventory =quantity;


SELECT i.name, m.name
FROM tblitems i, tblmeals m, tblpartof p
WHERE i.itemid =p.itemid and p.mealid =m.mealid;


SELECT v2.companyname
FROM tblvendors v1, tblvendors v2
WHERE v1.vendorid =v2.refferedby AND v1.companyname = 'Veggies_R_Us';


SELECT DISTINCT(companyname)
FROM tblingredients i,tblvendors v, tblmenuitems mi, tblmadewith mw
WHERE i.vendorid =v.vendorid AND i.ingredient id =mw.ingredientid AND mw.itemid =mi.itemid AND mi.name = 'Fruit plate'


SELECT companyname, i.vendorid, i.name
FROM tblvendors v FULL JOIN tblingredients i ON v.vendorid = i.vendorid;


SELECt DISTINCT mi.name, price
FROM tblngredients i, tblvendors v,tblmenuitems mi, tblmadewith mw
WHEREi.vendorid = v.vendorid AND i.ingredientid =mw.ingredientid AND mw.itemid = mi.itemid AND companyname = 'Veggies_R_Us';


SELECT v.vendorid AS "vendor Id", name
FROM tblingredients As i, tblvendors as v
WHERE i.vendorid = v.vendorid AND v.companyname IN (' Veggies_R_Us', 'Spring water supply')
ORDER BY v.vendorid;


SELECT tblstate.SUM(price)
FROM tblstores, tblorders
WHERE tblstores.storeid = tblorders.storeid
GROUP BY state;


SELECT tblstores.storeid, price
FROM tblorders, tblstores
WHERE tblorders.storeid = tblstores.storeid AND state = 'CA';













Sample Table: