CODEWITHSUNDEEP
sqlsql-serverleft-joincasesql-server-2014
mistaq
mistaq

Reputation: 385

SQL Server - Conditional LEFT JOIN with conditions on both sides

I'm trying to do a SELECT query with conditional joins, however there are conditions on both sides of the join, as depending on if a condition is met, then the table and columns being joined will be different. This is the query I'm attempting:

SELECT   per.Name, post.Category 
FROM     Person per
         LEFT JOIN WorkAddress wa ON per.WorkAddressID = wa.ID
         LEFT JOIN HomeAddress ha ON per.ID = ha.PersonID
         LEFT JOIN Postcode post ON 
         (CASE   WHEN per.WorkAddressID IS NOT NULL THEN wa.PostCodeID ELSE ha.PostCode+ha.Suburb END) =
         (CASE WHEN per.WorkAddressID IS NOT NULL THEN post.ID ELSE post.PostcodeSuburb END)

This is the data I'm working with:

Postcode:

[ID] INT IDENTITY (1,1) NOT NULL CONSTRAINT [PK_Postcode] PRIMARY KEY,
[Postcode] VARCHAR(4) NOT NULL,
[PostcodeSuburb] VARCHAR(100) NOT NULL,
[Category] INT NOT NULL

+----+----------+----------------+----------+
| ID | Postcode | PostcodeSuburb | Category |
+----+----------+----------------+----------+
|  1 |     1000 | 1000CityA      |        1 |
|  2 |     2000 | 2000CityB      |        2 |
+----+----------+----------------+----------+

WorkAddress:

[ID] INT IDENTITY (1,1) NOT NULL CONSTRAINT [PK_WorkAddress] PRIMARY KEY,
[Name] VARCHAR(50) NOT NULL,
[Address] VARCHAR(100) NOT NULL,
[PostCodeID] INT NOT NULL CONSTRAINT [FK_WorkAddress_PostCodeID] FOREIGN KEY REFERENCES Postcode(ID)

+----+-----------------+---------------+------------+
| ID |      Name       |    Address    | PostcodeID |
+----+-----------------+---------------+------------+
|  1 | CityA Town Hall | 10 Main Road  |          1 |
|  2 | CityB Palace    | 1 Palace Lane |          2 |
+----+-----------------+---------------+------------+

Person:

[ID] INT IDENTITY (1,1) NOT NULL CONSTRAINT [PK_Person] PRIMARY KEY,
[Name] VARCHAR(50) NOT NULL,
[WorkAddressID] INT NULL CONSTRAINT [FK_Person_WorkAddressID] FOREIGN KEY REFERENCES WorkAddress(ID)

+----+---------------+---------------+
| ID |     Name      | WorkAddressID |
+----+---------------+---------------+
|  1 | Johnny Smiles | 1             |
|  2 | Granny Smith  | NULL          |
|  3 | Smithee Black | 2             |
+----+---------------+---------------+

HomeAddress:

[ID] INT IDENTITY (1,1) NOT NULL CONSTRAINT [PK_HomeAddress] PRIMARY KEY,
[PersonID] INT NOT NULL CONSTRAINT [FK_HomeAddress_PersonID] FOREIGN KEY REFERENCES Person(ID),
[Address] VARCHAR(100) NOT NULL,
[PostCode] VARCHAR(4) NOT NULL,
[Suburb] VARCHAR(50) NOT NULL

+----+----------+----------------+----------+--------+
| ID | PersonID |    Address     | PostCode | Suburb |
+----+----------+----------------+----------+--------+
|  1 |        1 | 3 Little Road  |     1000 | CityA  |
|  2 |        2 | 80 Main Road   |     1000 | CityA  |
|  3 |        3 | 6 Village Lane |     2000 | CityB  |
+----+----------+----------------+----------+--------+

Currently I'm getting an error of Conversion failed when converting the varchar value '2000CityB' to data type int. This is despite the same condition being used in both CASE statements. I'm wondering if this can actually be done with CASE statements at all, or if I need to use a different method, since conditional JOINs with CASE do work if only one side has a CASE statement, not both.

And no, I'd prefer not to have to resort to changing the table structures altogether.

SQL Fiddle: http://www.sqlfiddle.com/#!18/56485/4

Upvotes: 4

Views: 2164

Answers (2)

Radim Bača
Radim Bača

Reputation: 10701

I believe that you just need to cast the int into varchar

SELECT    per.Name, post.Category 
FROM      Person per
          LEFT JOIN WorkAddress wa ON per.WorkAddressID = wa.ID
          LEFT JOIN HomeAddress ha ON per.ID = ha.PersonID
          LEFT JOIN Postcode post ON 
          (CASE WHEN per.WorkAddressID IS NOT NULL THEN CAST(wa.PostCodeID AS varchar(100)) ELSE ha.PostCode+ha.Suburb END) =
          (CASE WHEN per.WorkAddressID IS NOT NULL THEN CAST(post.ID AS varchar(100)) ELSE post.PostcodeSuburb END)

sqlfiddle

The problem, in this case, seems to be caused by fact that SQL Server always does the implicit conversion from varchar to int (if int and varchar are compared). You can try it with the following examples:

-- #1 example
with data as
(
 select 10 a, '10' b
)
select * from data where a = b;

-- #2 example
with data as
(
 select 10 a, 'b' b
)
select * from data where a = b;

dbfiddle

EDIT: as mentioned by @dnoeth, CASE requires that all return paths return the same data type and therefore SQL Server does the varchar conversion due to the int precedence (as shown on the above example).

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269753

I would suggest not using case at all in the on clause. Just use boolean logic:

ON (per.WorkAddressID IS NOT NULL AND wa.PostCodeID = post.ID) OR
   (per.WorkAddressID IS NULL AND ha.PostCode + ha.Suburb post.PostcodeSuburb)

This assumes that the types are compatible for the second comparison. If they are not, use CONCAT() or convert them explicitly to strings.

Upvotes: 1

Related Questions