Beginner SQL question (select statement)

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_at_ra.msstate.edu Received on Thu Dec 19 1996 - 00:00:00 CET