asked on 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;
Microsoft SQL ServerMicrosoft SQL Server 2008
Last Comment
Vitor Montalvão
What do you get if you were to try:
Select * From @table2 where ID = 4;
ASKER
There aren't any.
What I want is the email in table 1 where table2 ID 4 doesn't exost
What I want is the email in table 1 where table2 ID 4 doesn't exost
Your help has saved me hundreds of hours of internet surfing.
fblack61
SELECT t1.EmailAddress FROM @table1 t1
LEFT JOIN @table2 t2 ON t1.MailServerID = t2.MailServerID
WHERE IsNull( t2.MailServerID)
ASKER
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)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Vitor,
Yours still does not return a value when @ID=4
Yours still does not return a value when @ID=4
ASKER
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' );
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
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)
ASKER
Vitor... set @ID = 4 with your code. It returns nothing... yes?
ASKER CERTIFIED SOLUTION
Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a questionASKER
Thanks
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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;
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