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

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

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

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?

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Larry Brister

ASKER

Thanks

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