CODEWITHSUNDEEP
sql-serversql-server-2012
mHelpMe
mHelpMe

Reputation: 6668

check if id exists in multiple tables

I am using SQL Server 2012.

I have 5 tables (lets call them A, B, C, D & E). Each table contains a column called m_id, which contains id's that are nvarchar(10).

I currently run the query below 5 times (changing the table name). To see if the table contains the id.

select m_id from A where m_id = 'some_id'

Basically I want to know if the id is any of the 5 tables, if so return 1 else if does not exist in any of the 5 tables return 0.

I feel the current way I'm doing this is very inefficient. Is there a better way to do this?

Upvotes: 0

Views: 8821

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460208

You could use UNION(removes duplicates beforehand) or UNION ALL:

SELECT CASE WHEN EXISTS 
  ( SELECT 1 FROM ( SELECT m_id FROM  A 
                    UNION 
                    SELECT m_id FROM  B 
                    UNION 
                    SELECT m_id FROM  C 
                    UNION 
                    SELECT m_id FROM  D 
                    UNION 
                    SELECT m_id FROM  E ) All 
    WHERE  All.m_id = 'some_id') 
THEN 1 ELSE 0 END AS ContainsID

Upvotes: 5

Ionic
Ionic

Reputation: 3935

You can use this:

SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END as returnCode
FROM (
    SELECT m_id, N'A' as tableName FROM A WHERE m_id = 'some_id'
    UNION ALL
    SELECT m_id, N'B' as tableName FROM B WHERE m_id = 'some_id'
    UNION ALL
    SELECT m_id, N'C' as tableName FROM C WHERE m_id = 'some_id'
    UNION ALL
    SELECT m_id, N'D' as tableName FROM D WHERE m_id = 'some_id'
    UNION ALL
    SELECT m_id, N'E' as tableName FROM E WHERE m_id = 'some_id'
) data

Upvotes: 0

Related Questions