Fetching 3 tables at a time, retrieve only the table which matches the condition (SQL)

I have 3 tables: WATER_TENDER,ENGINE and TRAILER.

All of them have the EQ_ID as primary key and some other attributes, different for each table.

for example, EQ _ ID=’WT-123′ points a row inside the WATER _TENDER table only, and nothing inside the other tables.

As I do not know the EQ _ID in advance so I do not know which table to look at, how can I write a single query which checks all the 3 tables based on EQ _ID and retrieve just the table which matches the EQ _ID specified??

Answer

You can use UNION ALL to select rows from all tables and select all columns, providing NULL when that column doesn’t exist:

SELECT a, b, c, d, e, f FROM (
    SELECT a, b, NULL AS c, NULL AS d, NULL AS e, NULL AS f FROM WATER_TENDER
    UNION ALL
    SELECT NULL AS a, NULL AS b, c, d, NULL AS e, NULL AS f FROM ENGINE
    UNION ALL
    SELECT NULL AS a, NULL AS b, NULL AS C, NULL AS D, e, f FROM TRAILER) AS T1
WHERE _ID = @id

It’s a bit dirty though, IMHO. It feels like there’s something wrong with your design if you have to do this.