Search code examples
sqlsql-serversql-server-2016

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

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.

Solution

  • 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.