CODEWITHSUNDEEP
sql-server
A G
A G

Reputation: 22587

Check for an specific id or value in multiple tables

Lets say we have a Table A with primary key column ID. Now this table is in a foreign key relationship with say n other tables on this column.

I need to create a stored procedure which will take an ID as parameter and return 1 or 0 depending upon the value of ID present in any other table.

So for ex if ID =3 and any of the n tables contains 3 the SP should return 1 else 0.

What is the best way to achieve this?

One way is to run n different select queries and process every result. But looks quite clumsy.

Thanks.

Upvotes: 0

Views: 2645

Answers (2)

Meff
Meff

Reputation: 5999

IF EXISTS
(
    SELECT * 
    FROM 
        A
        INNER JOIN B ON A.Id = B.Id
        INNER JOIN C ON A.Id = C.Id
        INNER JOIN D ON A.Id = D.Id
)
BEGIN
    SELECT 1
END
ELSE
BEGIN
    SELECT 0
END

A fairly similar approach to Johann's. I'm sure it is possible to do this using the system tables, using the relationships found there, but that'd be a lot of effort. Will these tables change enough to make it worthwhile?

Upvotes: 1

Johann Strydom
Johann Strydom

Reputation: 1492

You can do something like this

select case when (isnull (b.id, 0)) = 0 then case when isnull (c.id, 0) = 0 then 0 else 1 end else 1 end
from a  
left outer join b on a.id = b.id 
left outer join c on a.id = c.id

just add one more join, etc.

Upvotes: 1

Related Questions