Re: Beginner SQL question (select statement)

If you intend to do serious work with ORACLE (or any RDBMS, for that matter), I strongly recommend that you learn data modelling principles, especially data normalization. Your Products table is not even in First Normal Form (1NF) when it should have been in 3NF. In 3NF, the Products table looks like this: (part_number, warehouse_id, qty_available), so there will be two records for the part number 123456, one for Houston warehouse and one for the Phoenix warehouse. The primary key (which uniquely identifies a row, or tuple, in a table) for the Products table is (part_number, warehouse_id). I hope you agree that the normalized table definition makes the query you want much easier to write (although it will be more complicated to put the two qty_available values in the same row, but somehow I don't think that this was your problem). There are other issues involved, especially if you have more than two warehouses, etc. BTW, I do not see any reason to de-normalize the Products table, if you indeed had it normalized initially. Please take it as friendly advice, not flaming or anything of that nature. My intent is to help, not discourage, you. As far as readings re data normalization, they are too numerous to name. Any decent college-level course on relational database theory must have at least one in its bibliography. I hope this helps. Michael Serbanescu ------------------------------------------------------------------------------------- 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