SQL Server - conditional LEFT JOIN with conditions on both sides

Question

I am trying to execute a SELECT query with conditional joins, however, there are conditions on both sides of the join, because depending on whether the condition is fulfilled, then the table and the columns to be joined will be different. This is the query I'm trying to do:

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

I am currently receiving an error message Conversion failed when converting the varchar value '2000CityB' to data type int.This is despite the same condition that is used in both CASE operations. I am wondering if this can really be done with CASE statements at all, or if I need to use a different method, since conditional JOINs with CASE work if only one side has a CASE statement and not both.

, .

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

Answer 1

, int 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

, -, , SQL Server varchar int ( int varchar). :

-- #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: @dnoeth, CASE, , , SQL Server varchar - int ( ).

Answer 2

case on. :

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

, . , CONCAT() .