Single SQL query to check if either table contains a row with column=x

Question

I have 2 unrelated tables A and B which both have foreign key constraints on C. I need to run an sql query that determines if either A or B contain a given id of C. My first approach was to use the union all but A and B are not related thus it will not work.

Any ideas?

Answer 1

Not 100% sure what you're asking but if you mean to return the elements of A and B that match the id in C then this will do it.

Select c.*, a.*, b.*
From c.id
    Left Outer Join a On a.id  = c.id
    Left Outer Join b On b.id = c.id
Where c.id = @somevalue and (a.id Is Not Null or b.id Is Not Null)

Where @somevalue is the value you're looking for.

Answer 2

Select 1 
From   DUAL
Where Exists ( Select null From Table_A Where a.fk = :id ) OR 
      Exists ( Select null From Table_B Where b.fk = :id );

Answer 3

You could indeed use union, why not? But why use UNION ALL, and not just UNION? Just pick the one common column:

SELECT 1 
FROM 
       (select A.fk from A inner join C on A.FK = C.pk 
        UNION 
        select B.fk from B inner join C on B.FK = C.pk) AS bothTables 
WHERE fk = 'desiredValue';

This would work just nicely.

Tested it on the following tables in MySQL, with myValue = 1, just to verify.

mysql> select * from A;
+------+--------+------+
| pk   | value  | fk   |
+------+--------+------+
|    1 | ape    |    2 | 
|    2 | fjfjfj |    3 | 
+------+--------+------+
2 rows in set (0.00 sec)

mysql> select * from B;
+------+--------+------+
| pk   | value  | fk   |
+------+--------+------+
|    1 | katt   |    1 | 
|    2 | fjfjfj |    3 | 
+------+--------+------+
2 rows in set (0.00 sec)

mysql> select * from C;
+------+-------+
| pk   | value |
+------+-------+
|    1 | hei   | 
|    2 | nei   | 
|    3 | jeg   | 
+------+-------+
3 rows in set (0.00 sec)