Re: Beginner SQL question (select statement)
Atif,
Here is a statement that will give you what your looking for.
select warehouse_location,qty_available1 from warehouse a,product b whereb.part_number='123456' and a.warehouseid=b.warehouse_id1
union
select warehouse_location,qty_available2 from warehouse a,product b whereb.part_number='123456' and a.warehouseid=b.warehouse_id2
of course a self-join would work also but the union statement seems easier
to maintain for me.
--
Joseph Cullipher |E-mail: joseph@cannonexpress.com
PO Box 364 |opinions express are those of my own and
Springdale, AR 72764 USA |don't necessarily reflect those of my company
On 19 Dec 1996, 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
}