CODEWITHSUNDEEP
c#asp.netsql-server
Infinity
Infinity

Reputation: 1325

How do I check for an ID value in more than one table

if not exists(SELECT 1 FROM MYTABLE1 WHERE ID=@ID)

BEGIN

END

I want to check for this ID value in MYTABLE2 as well..how should I write the IF condition ?? i want to check that a certain ID doesnt exist in any of the two tables.

Upvotes: 1

Views: 102

Answers (4)

Nabheet
Nabheet

Reputation: 1314

Depends on what you want -

If you want to check that the ID exists in either ONE of the tables then use UNION ALL. you could use JNK's answer.

If you want to check that the ID exists in both tables then use INNER JOIN.

If not exists (select top 1 from table1 a inner join Table2 b on a.ID = b.ID where a.ID = @ID) BEGIN END

Hope this helps.

Upvotes: 0

Paige Cook
Paige Cook

Reputation: 22555

You could do the following:

if (not exists(SELECT 1 FROM MYTABLE1 WHERE ID=@ID)) 
     AND (not exists(SELECT 1 FROM MYTABLE2 WHERE ID=@ID))

BEGIN

END

Upvotes: 3

Michael Christensen
Michael Christensen

Reputation: 1786

SELECT
    blah.ID
FROM
    MYTABLE1 as blah
WHERE
    blah.ID IN (some range of ints)

If you get no results then you know it does not exist

Upvotes: -1

JNK
JNK

Reputation: 65187

You could use an UNION ALL:

IF NOT EXISTS (SELECT 1 FROM
                        (SELECT ID FROM MyTable1
                         UNION ALL
                         SELECT ID FROM MyTable2) Table
               WHERE ID = @ID)
BEGIN
...
END

Upvotes: 3

Related Questions