Re: Beginner SQL question (select statement)

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.