2 table join and query help

[chipCHE]

Hello All,

I have two tables as follows (simplified for discussion):
Table 1 is named "Product" with the Fields:
"ProdName", "ProdDesc", "SupplierID1", "supplierID2"

Table 2 is named "Supplier" with the Fields:
"SupplierID", "CompanyName", "ContactFirstName", "phoneNumber"

I would like to join the tables by the SupplierID in the "Supplier" table to both the SupplierID1 and SupplierID2 fields in the "Product" Table

I would like to query the product table for entries where the SupplierID1 or SupplierID2 is > 0.
I would like to return a query that shows the "prodName", "ProductDesc" from the product table as well as all the supplier information for both Supplier1 and Supplier2.

I am sure some sort of alias on the Supplier Table is needed:

Any help would be greatly appreciated.

Regards,
Chip

 

[ESquared]

First of all, it would probably be best if you had a Products-To-Suppliers table. The current design makes for tough querying (as you probably found) and prevents you from adding more suppliers easily: adding an extra supplier means changing code which is bad.

I.e, instead of:

[tt]ProdID[/tt][tt] [/tt][tt]ProdName [/tt][tt] [/tt][tt]SupplierID1[/tt][tt] [/tt][tt]SupplierID2[/tt][tt]
1 R/C paperclip 27 15
2 microgalaxy 18 36[/tt]

You would use

[tt]ProdID[/tt][tt] [/tt][tt]ProdName [/tt][tt]
1 R/C paperclip
2 microgalaxy

[/tt][tt]ProdID[/tt][tt] [/tt][tt]SupplierID[/tt][tt]
1 27
1 15
2 18
2 36
2 87 <-- adding another supplier just means adding a row[/tt]

Now, to answer your question:

SELECT
      P.prodName,
      P.ProductDesc,
      S.*
   FROM Product P INNER JOIN Supplier S ON P.SupplierID1=S.SupplierID
UNION SELECT
      P.prodName,
      P.ProductDesc,
      S.*
   FROM Product P INNER JOIN Supplier S ON P.SupplierID2=S.SupplierID

But if you don't want each product to be listed once for each supplier then another way is (but this way is icky, imagine having 5 suppliers):

SELECT
      P.prodName,
      P.ProductDesc,
      S1.*,
      S2.*       
   FROM Product P
      LEFT JOIN Supplier S1 ON P.SupplierID1 = S1.SupplierID
      LEFT JOIN Supplier S2 ON P.SupplierID2 = S2.SupplierID
   WHERE
      S1.SupplierID IS NOT NULL
      OR S2.SupplierID IS NOT NULL

Note that if you used my suggestion above for how to design your tables, this is what your query would look like:

SELECT
      P.prodName,
      P.ProductDesc,
      S.*
   FROM Product P
      INNER JOIN ProductSuppliers PS ON P.ProductID = PS.ProductID
      INNER JOIN Supplier S ON Ps.SupplierID= S.SupplierID

Which would get all suppliers for each product no matter how many there are.

-E²

P.S. Don't use .* in your queries if you can avoid it. Rather, select all the columns. For one thing it avoids breaking stuff when you add a new column. For another, selecting unneeded columns is more expensive and can unnecessarily slow down your application.

 

[chipCHE]

Thank you Esquared,

However, the description I provided was not complete, I was already getting those results with:
SELECT
P.prodName,
P.ProductDesc,
S.*
FROM Product P INNER JOIN Supplier S ON P.SupplierID1=S.SupplierID or P.SupplierID2=S.SupplierID

I appologize.
I forgot to state that I need to return all the information for both suppliers on the same row of the query and that there will never be more than 2 suppliers for the same product.

I am looking for a result set with colum headers for the suppliers that look something like this:

"productName", "ProduceDesc", "SupplierCompany1", "SupplierPhone1", "SupplierCompany2", "SupplierPhone2"

If you or anyone else has any more suggestions I would be very grateful.

Regards,
Chip

 

[ESquared]

Chip,

You're in luck! I do have another suggestion for you! Re-read the answers I already gave you.

Here it is again:

SELECT
      P.prodName,
      P.ProductDesc,
      S1.*,
      S2.*       
   FROM Product P
      LEFT JOIN Supplier S1 ON P.SupplierID1 = S1.SupplierID
      LEFT JOIN Supplier S2 ON P.SupplierID2 = S2.SupplierID
   WHERE
      S1.SupplierID IS NOT NULL
      OR S2.SupplierID IS NOT NULL

But you must replace the S1.* and S2.* with the names of each column from the suppliers table and the alias you want.

[code]SELECT
      P.prodName,
      P.ProductDesc,
      S1.Company Supplier1Company,
      S1.Phone Suppler1Phone,
      S2.Company Supplier2Company,
      S2.Phone Suppler2Phone

You might have to put AS between the column and the alias depending on what version and drivers you are using.

To avoid having an empty S1 and a filled S2, you could first run an update to move the S2 supplier value to the S1 column, in case there are any of them.

-E

 

[chipCHE]

Thank you Esquared,

Sorry I did not get it the first time.

Regards,
Chip