MS SQL Server select from Sub Table
My code below works as long as @ID exists in @table2
But If I set @ID = 4
Then...nothing comes back when it need it to return test@server2.com
But If I set @ID = 4
Then...nothing comes back when it need it to return test@server2.com
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;
run this statement and see whats wrong there , i dont see any records associated with id = 4
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
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
Database and Application Developer
What do you get if you were to try:
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)
sr. Developer
Let me clarify
I need t2.EmailAddress if it exists
And t1.EmailAddres if it does not
Where t1.MailServerID = @MailServerID
I need t2.EmailAddress if it exists
And t1.EmailAddres if it does not
Where t1.MailServerID = @MailServerID
Try to replace the last SELECT with this one:
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)
This does work... I'm just wondering if there is something better
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' );
Anyway, I've tested your last code and mine previous suggestion and both returning the same:
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)
I don't know where you want to use your solution but one way to reduce the number of SELECTs is to check for number or returned rows (@@ROWCOUNT). If 0 then means nothing returned and you can perform the alternative SELECT. Btw, you might even not need to join with @table1 in this case:
SELECT EmailAddress
FROM @table2
WHERE MailServerID = @MailServerID AND ID = @ID;
IF @@ROWCOUNT = 0
SELECT EmailAddress
FROM @table1
WHERE MailServerID = @MailServerID;
Gain unlimited access to on-demand training courses with an Experts Exchange subscription.
Get AccessWhy Experts Exchange?
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
Jim Murphy
Programmer at Smart IT Solutions
When asked, what has been your best career decision?
Deciding to stick with EE.
Mohamed Asif
Technical Department Head
Being involved with EE helped me to grow personally and professionally.
Carl Webster
CTP, Sr Infrastructure Consultant
Did You Know?
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE
Ask ANY Question
Connect with Certified Experts to gain insight and support on specific technology challenges including:
- Troubleshooting
- Research
- Professional Opinions