MS SQL Server select from Sub Table

Larry Brister
Larry Brister used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009

Commented:
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 EghtebasDatabase and Application Developer

Commented:
What do you get if you were to try:

Select * From @table2 where ID = 4; 

Open in new window

Larry Bristersr. Developer

Author

Commented:
There aren't any.
What I want is the email in table 1 where table2 ID 4 doesn't exost
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Mike EghtebasDatabase and Application Developer

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

Open in new window

Larry Bristersr. Developer

Author

Commented:
Let me clarify
I need t2.EmailAddress if it exists
And t1.EmailAddres if it does not
Where t1.MailServerID = @MailServerID
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
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

Larry Bristersr. Developer

Author

Commented:
Vitor,
Yours still does not return a value when @ID=4
Larry Bristersr. Developer

Author

Commented:
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ãoIT Engineer
Distinguished Expert 2017

Commented:
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?
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
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 Bristersr. Developer

Author

Commented:
Vitor... set @ID = 4 with your code.  It returns nothing... yes?
IT Engineer
Distinguished Expert 2017
Commented:
Sure it's returning nothing and that's because ID=4 doesn't exist.
With that said, the way you presented (with the IF EXISTS...) is the only way to perform this.
Larry Bristersr. Developer

Author

Commented:
Thanks
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial