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

Question

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 1

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.

Answer 2

I don't think you can do that in a normal select query. If possible, I'd recommend either:

1. performing some logic so that you know which table you're supposed to look in (i.e., a 'WT-' means you should look in the WATER_TENDER table)
2. performing 3 separate queries, 1 for each table
3. redesigning your database (possibly have a master table that stores the relationships between an id and records the other tables)

Answer 3

If you decide to tackle the model a bit, you could use:

SELECT  Type, a ,b ,c ,d ,e ,f ,g ,h
FROM    Equipment AS eq
        LEFT JOIN ENGINE AS en ON en.EQ_ID = eq.EQ_ID
        LEFT JOIN TRAILER AS tr ON tr.EQ_ID = eq.EQ_ID
        LEFT JOIN WATER_TENDERER AS wt ON wt.EQ_ID = eq.EQ_ID
WHERE   eq.EQ_ID = 'WT-123'