How to join tables based on certain condition? SQL

There are three tables A,B,C

Table A has columns [ID], [flag], [many other columns]

Table B has columns [ID], [column subset of Table A]

Table C has columns [ID], [same column subset as Table B (thus also a subset of Table A), however with different values]

I want to join Table A & Table B if Flag = '1', and want to join Table A & Table C if Flag ='2'

Could you help me how I might be able to achieve this?

Many thanks!

Answer

You’re looking for a UNION.

SELECT
  <interesting columns>
FROM
  A
  JOIN
  B
  ON A.ID = B.ID 
  AND A.Flag = 1
UNION ALL
SELECT
  <exactly the same interesting columns>
FROM
  A
  JOIN
  C
  ON A.ID = C.ID 
  AND A.Flag = 2

If the flag is really a string column, put the single quotes back. If it’s numeric, leave them out.

Since the flag field in A should effectively eliminate duplicates between the result sets, I opted for UNION ALL, which is more efficient than UNION because UNION will run a DISTINCT under the covers, which in this case is likely unnecessary.