Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to figure out how to check if multiple rows exist in multiple tables in one statement.

Given the following tables:

table table_one 
========
 id
 'a'
 'b'
 'c'

table table_two
========
  id
 'aa'
 'bb'
 'cc'

This works,

SELECT 1 from table_one where id = 'a'

and

SELECT 1 from table_two where id = 'aa' 

works

How can I combine them into one statement?

I tried

 SELECT 1 FROM table_one WHERE id = 'a'
 UNION 
 SELECT 1 FROM  table_two WHERE id = 'aa'

but this just merges the results into one column so I cant tell if a particular table was missing a row.

What i'd like is a result row something this

  table_one  table_two  table_three    table_four
  =========  =========  ===========    ==========
  1          1          [null]         1
share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

UNION removes duplicate rows from the overall result, UNION ALL will preserve them.

To know from which table the result came, add a constant value:

SELECT 'one' as source, 
       1 
FROM table_one 
WHERE id = 'a'
UNION ALL
SELECT 'two',
       1 
FROM table_two 
WHERE id = 'aa'
share|improve this answer
    
works like a charm –  sqlnoob Mar 26 '13 at 13:07
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.