Filip Filip - 5 months ago 9
SQL Question

Combining Two SQL Queries with different where and returning unique rows

I'm trying to combine these 2 SQL queries.

SELECT * FROM `history` WHERE `ID` = 56

SELECT * FROM `live` WHERE `ID` = 56 AND `ACTIVE` = 0 AND t1.`Shown` = 'complete'


I've tried doing something like this:

SELECT t1.*,t2.*
FROM `history` t1
JOIN `live` t2
ON t1.`ID` = 56 AND t1.`ACTIVE` = 0 AND t1.`Shown` = 'complete'
WHERE t2.`ID` = 56


But the query above results in selecting everything from
live
and forgetting about
history
.

Expected result: I expect to select all columns from
history
and
live
where the
ID is equal to 56
. But there will be scenarios where the
ID's
in
history
don't exist in
live
-- in which case I want to select all from
history
where the
ID
is equal.

Both tables have different columns. The only thing that they have in common is the
ID
.

Solution:

Using UNION with specific columns from both tables.

Answer

If the history and live tables have the same number (and preferably type) of columns, then a UNION query might be what you want:

SELECT * FROM `history` WHERE `ID` = 56
UNION ALL
SELECT * FROM `live` WHERE `ID` = 56 AND `ACTIVE` = 0 AND t1.`Shown` = 'complete'

If the number of columns are not the same, then you can choose the same number of columns from both tables to make the query work.