DECLARE @table1 TABLE
(
MailServerID INT NULL ,
MailServer VARCHAR(100) ,
EmailAddress VARCHAR(100)
);
INSERT INTO @table1
( MailServerID, MailServer, EmailAddress )
VALUES ( 1, -- ID - int
'server1', -- MailServerID - int
'test@server1.com' ),
( 2, -- ID - int
'server2', -- MailServerID - int
'test@server2.com' );
DECLARE @table2 TABLE
(
ID INT IDENTITY(1, 1) ,
MailServerID INT ,
EmailAddress VARCHAR(100)
);
INSERT INTO @table2
( MailServerID, EmailAddress )
VALUES ( 2, -- MailServerID - int
'test@serversubtable_100.com' ),
( 2, -- MailServerID - int
'test@serversubtable_200.com.com' );
DECLARE @MailServerID INT;
SET @MailServerID = 2;
DECLARE @ID INT;
SET @ID = 1;
--If I set @ID = 4 nothing comes back when it should return test@server2.com
SELECT ISNULL(t2.EmailAddress, t1.EmailAddress) EmailAddress
FROM @table1 t1
LEFT JOIN @table2 t2 ON t1.MailServerID = t2.MailServerID
WHERE t1.MailServerID = @MailServerID
AND @ID = t2.ID;
Select * From @table2 where ID = 4;
SELECT t1.EmailAddress FROM @table1 t1
LEFT JOIN @table2 t2 ON t1.MailServerID = t2.MailServerID
WHERE IsNull( t2.MailServerID)
SELECT ISNULL(t2.EmailAddress, t1.EmailAddress) EmailAddress
FROM @table1 t1
LEFT JOIN @table2 t2 ON t1.MailServerID = t2.MailServerID
WHERE t1.MailServerID = @MailServerID
AND (t2.ID IS NULL OR t2.ID = @ID)
IF EXISTS ( SELECT 1 FROM @table2 WHERE MailServerID = @MailServerID AND ID = @ID )
BEGIN
SELECT ISNULL(t2.EmailAddress, t1.EmailAddress) EmailAddress
FROM @table1 t1
LEFT JOIN @table2 t2 ON t1.MailServerID = t2.MailServerID
WHERE t1.MailServerID = @MailServerID
AND @ID = t2.ID;
END;
ELSE
BEGIN
SELECT EmailAddress FROM @table1
WHERE MailServerID = @MailServerID;
END;
INSERT INTO @table2In your above example you didn't insert any ID=4 so how are you expecting to have any row returned with ID=4?
( MailServerID, EmailAddress )
VALUES ( 2, -- MailServerID - int
'test@serversubtable_100.com' ),
( 2, -- MailServerID - int
'test@serversubtable_200.com.com' );
DECLARE @ID INT;
SET @ID = 1;
--If I set @ID = 4 nothing comes back when it should return test@server2.com
SELECT ISNULL(t2.EmailAddress, t1.EmailAddress) EmailAddress
FROM @table1 t1
LEFT JOIN @table2 t2 ON t1.MailServerID = t2.MailServerID
WHERE t1.MailServerID = @MailServerID
AND (t2.ID IS NULL OR t2.ID = @ID)
IF EXISTS ( SELECT 1 FROM @table2 WHERE MailServerID = @MailServerID AND ID = @ID )
BEGIN
SELECT ISNULL(t2.EmailAddress, t1.EmailAddress) EmailAddress
FROM @table1 t1
LEFT JOIN @table2 t2 ON t1.MailServerID = t2.MailServerID
WHERE t1.MailServerID = @MailServerID
AND @ID = t2.ID;
END;
ELSE
BEGIN
SELECT EmailAddress FROM @table1
WHERE MailServerID = @MailServerID;
END;
EmailAddress
----------------------------------------------------------------------------------------------------
test@serversubtable_100.com
(1 row(s) affected)
EmailAddress
----------------------------------------------------------------------------------------------------
test@serversubtable_100.com
(1 row(s) affected)
SELECT EmailAddress
FROM @table2
WHERE MailServerID = @MailServerID AND ID = @ID;
IF @@ROWCOUNT = 0
SELECT EmailAddress
FROM @table1
WHERE MailServerID = @MailServerID;
SELECT ISNULL(t2.EmailAddress, t1.EmailAddress) EmailAddress, t2.ID, @id
FROM @table1 t1
LEFT JOIN @table2 t2 ON t1.MailServerID = t2.MailServerID
WHERE t1.MailServerID = @MailServerID