SQL Server - LEFT JOIN condicional con condiciones en ambos lados

SQL Server - LEFT JOIN condicional con condiciones en ambos lados

Estoy tratando de hacer una consulta SELECT con uniones condicionales, sin embargo, hay condiciones en ambos lados de la unión, ya que dependiendo de si se cumple una condición, la tabla y las columnas que se unen serán diferentes. Esta es la consulta que estoy intentando:

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)

Estos son los datos con los que estoy trabajando:

Código postal:

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

Dirección del trabajo:

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

Persona:

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

Dirección de casa:

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

Actualmente recibo un error de Conversion failed when converting the varchar value '2000CityB' to data type int. Esto es a pesar de que se usa la misma condición en ambas declaraciones CASE. Me pregunto si esto realmente se puede hacer con declaraciones CASE, o si necesito usar un método diferente, ya que las UNIONES condicionales con CASE funcionan si solo un lado tiene una declaración CASE, no ambos.

Y no, preferiría no tener que recurrir a cambiar las estructuras de las tablas por completo.

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

Mostrar la mejor respuesta

Considere usar AND y OR en lugar de CASE

Para su postcode parece que el campo está declarado como int. Como tal, para concatenar a un varchar, deberá emitirlo; p.ej. cast(ha.PostCode as nvarchar(9)) + ha.Suburb

Creo que solo necesitas convertir el int en 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

El problema, en este caso, parece deberse al hecho de que SQL Server siempre realiza la conversión implícita de varchar a int (si se comparan int y varchar). Puedes probarlo con los siguientes ejemplos:

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

EDITAR: como menciona @dnoeth, CASE requiere que todas las rutas de retorno devuelvan el mismo tipo de datos y, por lo tanto, SQL Server realiza la conversión de varchar debido a int precedencia (como se muestra en el ejemplo anterior).

Eso funciona, aunque me pregunto por qué es necesario para que funcione. Dado que las condiciones en ambos lados de JOIN son las mismas, pensé que si cumple la primera condición en ambos lados, wa.PostCodeID y post.ID siendo el mismo tipo INT significaría que no hay problema de unión (lo mismo con las columnas de condición ELSE, todas siendo VARCHAR).

@mistaq: CASE devuelve una sola columna y esa columna debe tener un solo tipo de datos. Cuando obtuvo varios tipos de datos devueltos por ENTONCES, el optimizador debe elegir uno. Lo numérico tiene prioridad sobre la cadena, ya que el 1 numérico puede representarse mediante diferentes cadenas, 1, 01, 1.0, etc.

Sugeriría no usar case en absoluto en la cláusula on. Solo usa la lógica booleana:

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

Esto supone que los tipos son compatibles para la segunda comparación. Si no lo son, use CONCAT() o conviértalos explícitamente en cadenas.