Re: Beginner SQL question (select statement)

On 19 Dec 1996 00:17:33 -0600, aak2@Ra.MsState.Edu (Atif Ahmad Khan) wrote: > >I have 2 tables. 1. Product 2. Warehouses. Products has the rows : >part_number, warehouse_id1, warehouse_id2, qty_available_1, qty_available_2 >values (123456, 100 , 200 , 500, 400) >Warehouses has : warehouseid, warehouse_location. >values (100 , Houston > 200 , Phoenix) > >I need a select statement (or any other simple solution) that would let >me search the database for the availability of a product. For example >with the above sample data I would like to be able to get results like : > >result for : '123456' > >warehouse_location qty_available_1 qty_available_2 >------------------ --------------- --------------- >Houston 500 >Phoenix 400 > >As you can probably tell, replacing the warehouse-ids with values from >another table is the difficult part for me. > >The following statement does half of the work : > >select warehouse_location, qty_availability_1 from (select * from Products >where part_number=123456) a, Warehouses b where a.warehouse_id1=b.warehouse_id> >It returns the follwing result : >Houston 500 > >Now I am not sure if I am going in the right direction and how would I get the >2nd line listed. I would appreciate any hints. Thanks a million. > >Atif Khan >aak2@ra.msstate.edu Assuming this is an academic question: You've probably been told by now that the main cause of your difficulty is the database design ... you're gonna be really screwed when your "company" opens that third warehouse in the Bronx! Your Products table should be part_number, warehouse_id, quantity_available The query would be simple and return 2 rows in your example: Select Products.part_number, Products.quantity_available,Warehouses.warehouse_location from OUTER products, Warehouses ON Products.warehouse_id = WareHouses.WarehouseID where Products.part_number="123456" If you must deal the with ugly reality of the schema as it is presented (don't we all?) then there is no easy way, but it can be done: Select Products.part_number, Products.quantity_available1,Warehouses.warehouse_location from OUTER products, Warehouses ON Products.warehouse_id1 = WareHouses.WarehouseID where Products.part_number="123456" UNION Select Products.part_number, Products.quantity_available2,Warehouses.warehouse_location from OUTER products, Warehouses ON Products.warehouse_id2 = WareHouses.WarehouseID where Products.part_number="123456" Seems everyone forgets the UNION operator!