CODEWITHSUNDEEP
sqlsql-serverselect
BobSki
BobSki

Reputation: 1552

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

Upvotes: 1

Views: 129

Answers (2)

mendosi
mendosi

Reputation: 2051

Here is an alternative solution, as described by ebyrob:

SELECT  t1.ProductID, 
        t2.Name as [Product Type], 
        t3.Name as [Product Category], 
        IsNull(t4.Name, 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 
LEFT Join tblReferences t4 on t1.ProductProvider = t4.ID
LEFT Join tblProductSeller t6 on t1.ProductProvider = t6.ID;

What this will do is show the Name from tblReferences if there is one, otherwise it will show the Name from tblProductSeller. You could reorder those depending upon your requirements. You may also wish to add a predicate like

WHERE t4.ID IS NOT NULL OR t6.ID IS NOT NULL

This will avoid returning multiple rows in the situation where there is a match in both tblReferences and tblProductSeller.

Upvotes: 1

Michael buller
Michael buller

Reputation: 586

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

Upvotes: 2

Related Questions