Join with many parts into only 1 request

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

Table 2: T_Supplier
IdSupplier
SupplierName
Adress
....

For one product we can have many supplier.

How to received its name(ProductName) and names of each suppliers
(SupplierName) for 1 product into only 1 request ??

Thanks
Best regards.

Re: Join with many parts into only 1 request

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

Re: Join with many parts into only 1 request

leupat a écrit :
> 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
>
> Table 2: T_Supplier
> IdSupplier
> SupplierName
> Adress
> ...
>
> For one product we can have many supplier.
>
> How to received its name(ProductName) and names of each suppliers
> (SupplierName) for 1 product into only 1 request ??
>
> Thanks
> Best regards.
Bonjour,


with union (but some limits ???)


select ProductName, SupplierName from T_product,T_Supplier where
IdSupplierPart1=IdSupplier
union
select ProductName, SupplierName from T_product,T_Supplier where
IdSupplierPart2=IdSupplier
union
select ProductName, SupplierName from T_product,T_Supplier where
IdSupplierPart3=IdSupplier
...
...
...
union
select ProductName, SupplierName from T_product,T_Supplier where
IdSupplierPart10=IdSupplier
order by ProductName, SupplierName;

GR

Re: Join with many parts into only 1 request

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
>
> Table 2: T_Supplier
> IdSupplier
> SupplierName
> Adress
> ...
>
> For one product we can have many supplier.
>
> How to received its name(ProductName) and names of each suppliers
> (SupplierName) for 1 product into only 1 request ??
>
> Thanks
> Best regards.

This is a classic example of a non-normalized table, and SQL will not
handle this well for very good reasons. Remove IdSupplierPart1 etc from
T_Product and create an additional table linking Supplier to Product.

T.