Bobski Bobski - 1 month ago 15
SQL Question

SQL query UNION to another tables to select data

I have the following query....

SELECT t1.ProductID, t2.Name as [Product Type], t3.Name as [Product Category],
t4.Name as [Product Provider]
From tblProducts t1
Inner Join tblReferences t2 on t1.ProductType = t2.ID
Inner Join tblReferences t3 on t1.ProductCategory = t3.ID
Inner Join tblReferences t4 on t1.ProductProvider = t4.ID


I know this is not the prettiest query, but it does the job, and most likely won't be edited once its finalized. Now I'm trying to do an INNER JOIN with another Table and here's where the issue comes...

So I'm trying to to PULL data from another table (tblProductSeller), however I'd need it to show as [Product Provider]

so to add on the the previous SELECT statement...

SELECT t1.ProductID, t2.Name as [Product Type], t3.Name as [Product Category],
t4.Name as [Product Provider],

t6.SellerName as [Product Provider] <---new line -


however this displays it as a new column, I'm trying to display it in the same column as

t4.Name as [Product Provider]


the rest of the query I tried is

Inner Join tblReferences t2 on t1.ProductType = t2.ID
Inner Join tblReferences t3 on t1.ProductCategory = t3.ID
Inner Join tblReferences t4 on t1.ProductProvider = t4.ID
Inner Join tblProductSeller t6 on t1.ProductProvider = t6.ID


Is something like that even possible - do I have to do an UNION?

EDIT . To keep it short I'll abandon most of the fields with the exception fo the one I'm having an issue with.

TblProducts
ProductID ProviderID
17 16
18 20
19 24

tblReferences
ID Name
16 Microsoft
20 ADP

tblProductSeller
ID ProductProvider
24 Apple


TblReferences has the names of some standard Sellers, however, I need to be able to reference tblProductSeller, since alot of the providers will be added in there. Basically that's the place where they'll be added. All my data is stored in tblProducts.

My entire goal is to be able to populate a DataGridView, however, not have any ID's but rather be able to reference both tblReferences, and tblProductSeller.

So my end result would look like


ProductID Contact Provider
16 Microsoft
20 ADP
24 Apple


Answer

Depending on the use case as to which tables are needed the statements would need to be adjusted below, but just to get your wheels turning... here is an example.

SELECT t1.ProductID, t2.Name as [Product Type], t3.Name as [Product Category], 
t4.Name as [Product Provider]
From  tblProducts t1 
Inner Join tblReferences t2 on t1.ProductType = t2.ID 
Inner Join tblReferences t3 on t1.ProductCategory = t3.ID 
Inner Join tblReferences t4 on t1.ProductProvider = t4.ID 
UNION 
Select t1.ProductID, t2.Name as [Product Type], t3.Name as [Product Category], 
t6.Name as [Product Provider]
From  tblProducts t1 
Inner Join tblReferences t2 on t1.ProductType = t2.ID 
Inner Join tblReferences t3 on t1.ProductCategory = t3.ID  
Inner Join tblProductSeller t6 on t1.ProductProvider = t6.ID