CODEWITHSUNDEEP
sqlsql-server-2008
Nick
Nick

Reputation: 7525

Conditional INNER JOIN in SQL Server

I have a rather complex query that pretty much mimics a test query I have below:

SELECT C.*
  FROM Customer C 
       INNER JOIN CustDetail CD ON C.CustomerId = CD.CustomerId
       INNER JOIN Address A ON CD.DetailID = A.DetailID
       INNER JOIN Group G ON C.CustomerId = G.CustomerId   --Join only when C.code = 1
       INNER JOIN GroupDetail D ON G.GroupId = D.DetailId  --Join only when C.code = 1
WHERE G.Active = 1 AND        --Only when C.code = 1
      D.code = '1' AND        --Only when C.code = 1
      C.Id = @customerId

I'd like to do INNER JOINs on Group G and GroupDetail D (and ofcourse not have them in the WHERE conditions based on the table column C.code = 1

I replaced the INNER JOINs with LEFT OUTER JOINs for both the join conditions, but the result set is not what was expected

How do I conditionally do the JOIN

Upvotes: 0

Views: 13459

Answers (3)

Andrew
Andrew

Reputation: 4624

SELECT C.*
  FROM Customer C 
       INNER JOIN CustDetail CD ON C.CustomerId = CD.CustomerId
       INNER JOIN Address A ON CD.DetailID = A.DetailID
       LEFT OUTER JOIN Group G ON C.CustomerId = G.CustomerId
       LEFT OUTER JOIN GroupDetail D ON G.GroupId = D.DetailId
WHERE ((G.Active = 1 AND C.code = 1) OR G.Active IS NULL) AND
      ((D.code = '1' AND C.code = 1) OR D.code IS NULL) AND
      C.Id = @customerId

I'm guessing you didn't include the IS NULL checks before so you never got to see rows where C.code <> 1 ?

You should check for NULL on a field that will never be null. This is almost always 'id', but it's not clear that you have a G.id or a D.id.

Upvotes: 4

Magnus
Magnus

Reputation: 46929

This will do a semi-join only when code is 1.

SELECT C.*
FROM Customer C 
    INNER JOIN CustDetail CD 
        ON C.CustomerId = CD.CustomerId
    INNER JOIN Address A 
        ON CD.DetailID = A.DetailID
WHERE 
      C.Id = @customerId AND
      (c.code != 1 OR
      EXISTS(
         SELECT NULL
         FROM Group G
            JOIN GroupDetail D ON G.GroupId = D.DetailId
         WHERE
            C.CustomerId = G.CustomerId AND
            G.Active = 1 AND 
            D.code = '1'
      ))

Upvotes: 0

Brian Arsuaga
Brian Arsuaga

Reputation: 358

I'm guessing what you want is just a tighter ON clause, and a compound condition.

SELECT C.*
  FROM Customer C 
       INNER JOIN CustDetail CD ON C.CustomerId = CD.CustomerId
       INNER JOIN Address A ON CD.DetailID = A.DetailID
       -- the next two joins happen only when c.code=1
       -- their columns will be null when there is no match.
       LEFT JOIN Group G ON C.CustomerId = G.CustomerId AND C.Code = 1
       LEFT JOIN GroupDetail D ON G.GroupId = D.DetailId AND C.Code = 1
WHERE C.Id = @customerId AND --always check this
      -- this condition is true if code is null or code isn't 1,
      ((C.code IS NULL or C.code <> 1)
      -- or (if the code is 1), it is true if g.active and d.code
       OR (G.Active = 1 AND D.code = '1'))

Upvotes: 0

Related Questions