hi, i have few tables as follow :

CUSTOMERS CUSTID CUSTFAMNAME CUSTGIVENNAME CUSTSUBURB
EMPLOYEES EMPLOYEEID ...... CUSTSUBURB
ITEMS ITEMID SUPPLIERID DESCRIPTION ...
ORDERDETAILS ORDERID ITEMID QUANTITY SALEPRICE
ORDERS ORDERID CUSTID EMPLOYEEID ORDER_DATE
SUPPLIERS SUPPLIERID ..... SUPPPOSTCODE


question
To save money, Dave thinks it might be a good idea if he gets his employees to
pick up products directly from the supplier and take them directly to the customers when
they have placed an order. This is only reasonable, however, if the customer, supplier and
employee all live in the same suburb. Generate a list of EmployeeIDs, CustomerIDs,
ItemIDs and SupplierIDs that will match employees, customers and suppliers who are all
in the same suburb and will show the items that need to be delivered (note: this will
obviously only work if an order has been placed matching all three participants) [2
marks].

actualy they went me to list empID, suppID, custID, ItemID if theyhave
the same suburb.
if they have an order, so i tried something like this

SELECT CUSTOMERS.CUSTID, EMPLOYEES.EMPLOYEEID, ITEMS.ITEMID,
SUPPLIERS.SUPPID

FROM ORDERS, SUPPIERS, ITEMS
WHERE (SUPPLIER.SUPPSUBURB = EMPLOYEES.EMPSUBURB) AND
(EMPLOYEES.EMPSUBURB = CUSTOMERS.CUSTOMERID) AND (CUSTOMERS.CUSTOEMRID
= ORDERS.CUSTOMERID)



well it will be nice to help me a little
thank you


Oracle LazyDBA home page