Avatar of Larry Brister
Larry Brister
Flag for United States of America 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

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;

Open in new window

Microsoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
Vitor Montalvão

8/22/2022 - Mon
Aneesh

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
Mike Eghtebas

What do you get if you were to try:

Select * From @table2 where ID = 4; 

Open in new window

Larry Brister

ASKER
There aren't any.
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
Mike Eghtebas

SELECT t1.EmailAddress FROM @table1 t1
        LEFT JOIN @table2 t2 ON t1.MailServerID = t2.MailServerID
WHERE IsNull( t2.MailServerID) 

Open in new window

Larry Brister

ASKER
Let me clarify
I need t2.EmailAddress if it exists
And t1.EmailAddres if it does not
Where t1.MailServerID = @MailServerID
Vitor Montalvão

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)

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Larry Brister

ASKER
Vitor,
Yours still does not return a value when @ID=4
Larry Brister

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;

Open in new window

Vitor Montalvão

INSERT  INTO @table2
        ( MailServerID, EmailAddress )
VALUES  ( 2,  -- MailServerID - int
          'test@serversubtable_100.com' ),
        ( 2,  -- MailServerID - int
          'test@serversubtable_200.com.com' );
In your above example you didn't insert any ID=4 so how are you expecting to have any row returned with ID=4?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Vitor Montalvão

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;

Open in new window


EmailAddress
----------------------------------------------------------------------------------------------------
test@serversubtable_100.com

(1 row(s) affected)

EmailAddress
----------------------------------------------------------------------------------------------------
test@serversubtable_100.com

(1 row(s) affected)

Open in new window

Larry Brister

ASKER
Vitor... set @ID = 4 with your code.  It returns nothing... yes?
ASKER CERTIFIED SOLUTION
Vitor Montalvão

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
Sign up - Free for 7 days
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 question
Larry Brister

ASKER
Thanks
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Vitor Montalvão

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;

Open in new window