Re: Beginner SQL question (select statement)

On 19 Dec 1996 00:17:33 -0600, aak2_at_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_at_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! Received on Tue Dec 24 1996 - 00:00:00 CST