how to join with IIF function?

Hi I am using MSSQL 2017 and try to figure out how this code works

DECLARE @customer TABLE (

    credithold bit,
    id int primary key, 
    deliverycityID int,
    postalcityID int
)
DECLARE @city TABLE (

    cityid int primary key, 
    pop bigint
)
INSERT INTO @city VALUES (1,2666),(2,7889),(3,28917)
INSERT INTO @customer VALUES(1,1,2,1),(0,2,3,2),(1,3,1,3),(0,4,2,3)

SELECT c1.id, pop, IIF(credithold = 0, deliverycityID, postalcityID) as cityid
FROM @customer c1
INNER JOIN @city AS A ON A.cityid = cityid

The code gives result like (https://i.stack.imgur.com/qxs2G.png) But I think it should return like this (https://i.stack.imgur.com/x3B19.png) https://i.stack.imgur.com/x3B19.png Any advice? Thank you!

Sorry for the image, I can’t post image directly because of not enough reputation.

Answer

The right join by doing this just do this:

SELECT c1.id, pop, IIF(credithold = 0, deliverycityID, postalcityID) as cityid
FROM @customer c1
INNER JOIN @city AS A ON A.cityid = IIF(credithold = 0, deliverycityID, postalcityID)

Advertisement

Answer