We help IT Professionals succeed at work.

MS SQL Server select from Sub Table

302 Views
Last Modified: 2018-03-21
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

AneeshDatabase Consultant
CERTIFIED EXPERT
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
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
CERTIFIED EXPERT
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
CERTIFIED EXPERT
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
CERTIFIED EXPERT
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
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Larry Bristersr. Developer

Author

Commented:
Thanks
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why 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
Empower Your Career
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