johna johna - 3 months ago 4
SQL Question

Is there a way of this query working with JOINs or must I use a UNION?

I am probably trying to use JOINs for purposes they were not intended here.

Here's my (simplified) table structure:

Table A


  • ID

  • Table C ID

  • IsStatic (bit)



Table B


  • ID

  • Table A ID (nullable)

  • Table C ID



Table C


  • ID



My goal is to get all of Table B rows joined to Table A rows where Table B's Table A ID column has a value and equals Table A's ID column value.

I also need all of Table B rows where Table B's Table A ID column has no value.

I also need all of Table A rows with there were no joined Table B rows and Table A's IsStatic column is true.

Table C must also be associated with Table A or Table B. If Table B does not have a value for TableAID then it's value for TableCID should equal TableC's ID value. Otherwise TableA's TableCID should equal TableC's ID value.

Here's some SQL to create some TABLE variables and populate with sample data:

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)


Here's my (simplified) query that didn't quite work:

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))


The result of this query using the sampel data is:

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


The required result is:

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


Problem with this query is that if TableB.TableAID has no value then the Table A rows where TableA.IsStatic is true without any matching TableB rows are never included. Also some TableB rows are being included and they shouldn't be.

The only other way I can see of doing this is with a
union
with a
not exists
but I was hoping to do this in a more efficient way.

Update: Adding a WHERE clause removes the "not required" rows but still omits the missing row.

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


The result of the same query with the where clause is:

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

Answer

What a mind twister. I think that this is another way to express it. You'll have to see if the performance is good or not:

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

I should also mention that it's hard to know for sure if the above query really respects your requirements using the sample data you provided. To illustrate, if I use this simplified query below that simply ignores the @TableC table, I still get the right results with your sample data:

 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

EDIT: Funny discussion in the comments about the interpretation of OP' requirements... But if I had to address Anton's point:

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)
Comments