In SQL How to choose two of three or more tables to join based on a condition?-CodePudding

I have a total of say 3 tables, but I am only interested in joining two of them based on a condition. There is a lot of Q&A on how to use Case Statements, but that's when tables are already defined. My case is that DEPENDING on my condition I may choose one or the other table. Here is my tables:

The outcome:

Product Type    Product Price   Product-Related Info
A               10              A Definition
A               10              A Definition
B               15              B Definition
C               30              C Definition
C               30              C Definition
C               30              C Definition

Table A, B, C (three tables having same schema and structure):

Table A 
[Product]   [Some info]
A           Definition of A

And then Table B, C, etc.

Price table has two columns: Price and Product (A, B, C)

My confusion is that I am not sure how to join two tables, when I have one which is Price, but then the other one could be any of the remaining three based on the condition. Case statement doesn't work as it requires you to have a final table.

The keys: I should have done included more information on keys. The challenge is really that if the value of column Product type is A, I need to query table A, if it is B , I need to query B. That's the main challenge.

CodePudding user response：

You can join tables with conditions. Just add the condition to your Join statement. Here is an example:

SELECT 
...
COALESCE(A.Price, B.Price, C.Price)
...
FROM Product P
LEFT OUTER JOIN TableA A ON A.ProductId = P.ProductId AND YourConditionA
LEFT OUTER JOIN TableB B ON B.ProductId = P.ProductId AND YourConditionB
LEFT OUTER JOIN TableC C ON C.ProductId = P.ProductId AND YourConditionC

With COALESCE you can select the first not null value.

CodePudding user response：

You can left join all three and then select the non-null one using COALESCE(). For example:

select
  p.*,
  coalesce(a.someinfo, b.someinfo, c.someinfo) as info
from price p
left join tablea a on a.product = p.product
left join tableb b on b.product = p.product
left join tablec c on c.product = p.product

CodePudding user response：

While you could write condition logic into the joins you might find this to be an acceptable solution. It would spare a lot of coalesce() operations across the various columns:

select *
from TableA a cross apply (
    select * /* better to list the columns individually */
    from TableB b
    where a.ProductType = 'X'
    union all
    select *
    from TableC c
    where a.ProductType = 'Y'
) T

CodePudding user response：

First thing to do is to split Price.product into three separate columns: productA, productB, and productC. They will be much easier to work with that way. I recommend you make a new table and copy the data over. After that, doing the three LEFT OUTER JOIN method can be performed without any fancy sql:

SELECT 
price, productA, productB, productC
FROM Prices P
LEFT OUTER JOIN TypeAProducts TA ON TA.ProductId = P.productA
LEFT OUTER JOIN TypeBProducts TB ON TB.ProductId = P.productB
LEFT OUTER JOIN TypeCProducts TC ON TC.ProductId = P.productC;

In short, use the way that an outer join works (it preserves the table to the left of the LEFT OUTER JOIN operator) to join to all three other tables in one step.