|
|
Posted by J.O. Aho on 02/18/06 02:57
leupat wrote:
> Hello everybody !!
>
> I have a little problem with request.
>
> I have 2 tables like that :
>
> Table 1: T_Product
> IdProduct
> ProductName
> IdSupplierPart1
> IdSupplierPart2
> IdSupplierPart3
> ...
> IdSupplier10
What if you have more than 10 suppliers?
You should have a relation-table for your T_Product and T_Supplier
> Table 2: T_Supplier
> IdSupplier
> SupplierName
> Adress
> ...
Table 1: T_Product
IdProduct
ProductName
Table 2: T_Supplier
IdSupplier
SupplierName
Adress
....
Table 3: T_Relations
IdProduct
IdSupplier
> For one product we can have many supplier.
This is a more proper way to make it and makes it easier to join tables as you
have only one column that needs to be joined.
> How to received its name(ProductName) and names of each suppliers
> (SupplierName) for 1 product into only 1 request ??
SELECT * FROM T_Supplier LEFT JOIN T_Relations ON T_Supplier.IdSupplier =
T_Relations.IdSupplier WHERE T_Relations.IdProduct = 'productid';
This should list all your suppliers if you use the 3 table system that you get
if you normalize your database. (could be that you need a RIGHT JOIN, always
forget which way, but you notice on the result).
This works in all sql servers I know of.
If you want to use your stiff 2 table system you need to have the table in a
sql server that supports subselects
SELECT * FROM T_Supplier WHERE IdSupplier IN(SELECT IdSupplier1, IdSupplier2,
IdSupplier3, IdSupplier4, IdSupplier5, IdSupplier6, IdSupplier7, IdSupplier8,
IdSupplier9, IdSupplier10 FROM T_Product WHERE IdProduct='productid');
(not sure if thats all correct, but should give you a sense of the sql query).
//Aho
Navigation:
[Reply to this message]
|