Re: Beginner SQL question (select statement)
Date: 1996/12/19
Message-ID: <32B911C7.2431_at_adra.se>#1/1
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.
>
> 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 :
Hello Atif! Well, I believe you suffer from poor database design. The
product table should look like (part_number, warehouse_id,
qty_available)
but given the circumstances you will be helped by this select statement:
SELECT
p.part_number,
w1.warehouse_location warehouse_1,
p.qty_available_1,
w2.warehouse_location warehouse_2,
p.qty_available_2
FROM
products p,
warehouses w1,
warehouses w2
WHERE
w1.warehouse_id = p.warehouse_id_1 AND
w2.warehouse_id = p.warehouse_id_2
This will give you one row per product. You wrote two in you example and
this
you can achieve using a union of two selects but this is another story.
-- Sport radio: people listening to people watching people having fun Mailto:roger.wernersson_at_adra.se BTW: All opinions are mine, all mine, and nobody's but mine.Received on Thu Dec 19 1996 - 00:00:00 CET