Is there a way for this request to work with JOINs or should I use UNION?

Question

I'm probably trying to use JOINs for purposes that they are not intended here.

Here is my (simplified) table structure:

Table a

• ID
• Table id
• IsStatic (bit)

Table B

• ID
• Table A ID (with a null value)
• Table id

Table C

• ID

My goal is to get all the rows of table B connected to the rows of table A, where Table B is Table. The identity column has a value and is equal to the value of the column in table A.

I also need all the rows of table B, where table B table identifier does not matter.

I also need all the rows of table A that didn't have the joined rows of table B, and the IsStatic column column is true.

C A B. B TableAID, TableCID TableC ID. TableA TableCID TableC ID.

SQL TABLE :

DECLARE @TableA TABLE (TableAID int, TableCID int, IsStatic bit)
DECLARE @TableB TABLE (TableBID int, TableAID int, TableCID int)
DECLARE @TableC TABLE (TableCID int)

INSERT INTO @TableC (TableCID) VALUES (1)
INSERT INTO @TableC (TableCID) VALUES (2)

INSERT INTO @TableA (TableAID, TableCID, IsStatic) VALUES (1, 1, 0)
INSERT INTO @TableA (TableAID, TableCID, IsStatic) VALUES (2, 2, 1)
INSERT INTO @TableA (TableAID, TableCID, IsStatic) VALUES (3, 2, 1)
INSERT INTO @TableA (TableAID, TableCID, IsStatic) VALUES (4, 2, 0)

INSERT INTO @TableB (TableBID, TableAID, TableCID) VALUES (1, NULL, 1)
INSERT INTO @TableB (TableBID, TableAID, TableCID) VALUES (2, 1, 1)
INSERT INTO @TableB (TableBID, TableAID, TableCID) VALUES (3, 2, 2)

() , :

SELECT
    a.TableAID,
    b.TableBID
FROM @TableC c
LEFT OUTER JOIN @TableB b ON
    (b.TableAID IS NOT NULL OR (b.TableAID IS NULL AND b.TableCID = c.TableCID))
LEFT OUTER JOIN @TableA a ON
    a.TableCID = c.TableCID
    AND ((a.IsStatic = 1 AND b.TableBID IS NULL)
        OR (b.TableBID IS NOT NULL AND b.TableAID = a.TableAID))

sampel:

TableAID TableBID
-----------------
NULL     1
1        2
NULL     3       (not required)
NULL     2       (not required)
2        3

:

TableAID TableBID
-----------------
NULL     1
3        NULL     (missing)
2        3
1        2

, TableB.TableAID , A, TableA.IsStatic , - TableB, . TableB, .

, , union not exists, .

: WHERE " " , .

WHERE (b.TableBID IS NULL OR b.TableAID IS NULL OR b.TableAID = a.TableAID)

where:

TableAID TableBID
-----------------
NULL     1
1        2
2        3

Answer 1

. , . , :

select a.TableAID, b.TableBID
  from (select a.*
          from @TableA a
          join @TableC c
            on c.TableCID = a.TableCID) a
  full outer join (select b.*
                     from @TableB b
                     join @TableC c
                       on c.TableCID = b.TableCID) b
    on b.TableAID = a.TableAID
 where b.TableBID is not null or a.IsStatic = 1

, , , . , , @TableC, :

 select a.TableAID, b.TableBID
  from @TableA a
  full outer join @TableB b
    on b.TableAID = a.TableAID
 where b.TableBID is not null or a.IsStatic = 1

: OP... :

select a.TableAID, b.TableBID
  from (select a.*, 
               case when c.TableCID is not null then 1 end as has_c
          from @TableA a
          left join @TableC c
            on c.TableCID = a.TableCID) a
  full outer join (select b.*,
                          case when c.TableCID is not null then 1 end as has_c
                     from @TableB b
                     left join @TableC c
                       on c.TableCID = b.TableCID) b
    on b.TableAID = a.TableAID
 where (b.TableBID is not null or a.IsStatic = 1)
   and (a.has_c = 1 or b.has_c = 1)

Answer 2

select b.TableAID, b.TableBID
from @TableB b
    left join @TableA a on a.TableAID = b.TableAID
    inner join @TableC c on c.TableCID = case when a.TableAID IS NULL then b.TableCID else a.TableCID end

union all

select a.TableAID, NULL
from @TableA a
    inner join @TableC c on c.TableCID = a.TableCID
    left join @TableB b on b.TableAID = a.TableAID
where b.TableAID is NULL
    and a.IsStatic = 1