SQLite Forum

How can I merge results from two SELECTs?
Login

How can I merge results from two SELECTs?

(1) By PazO (JohnSmith) on 2021-05-25 14:29:06 [link] [source]

Hello,

Assuming I have the following table:

CREATE TABLE SnapshotParameters(
	snapshot_id   integer,
	param_id      integer,
	param_value   integer
);

In this table I hold the following data:

-- Original snapshot (values are 1)
INSERT INTO SnapshotParameters (snapshot_id, param_id, param_value) VALUES ( 1, 1, 1);
INSERT INTO SnapshotParameters (snapshot_id, param_id, param_value) VALUES ( 1, 2, 1);
INSERT INTO SnapshotParameters (snapshot_id, param_id, param_value) VALUES ( 1, 3, 1);
INSERT INTO SnapshotParameters (snapshot_id, param_id, param_value) VALUES ( 1, 4, 1);
INSERT INTO SnapshotParameters (snapshot_id, param_id, param_value) VALUES ( 1, 5, 1);
-- Edited snapshot (values are 2)
INSERT INTO SnapshotParameters (snapshot_id, param_id, param_value) VALUES ( 2, 3, 2);
INSERT INTO SnapshotParameters (snapshot_id, param_id, param_value) VALUES ( 2, 4, 2);

Now I want to run a query that will return to me one result per parameter-id with the following logic:
If parameters exists in snapshot-2 then return this value,
Otherwise - return value from snapshot-1.

Result example:

1, 1, 1
1, 2, 1
2, 3, 2
2, 4, 2
1, 5, 1

Thanks for any tip,
PazO

(2) By Igor Tandetnik (itandetnik) on 2021-05-25 14:41:44 in reply to 1 [link] [source]

Something along these lines:

select ifnull(t2.snapshot_id, t1.snapshot_id), t1.param_id, ifnull(t2.param_value, t1.param_value)
from SnapshotParameters t1 left join SnapshotParameters t2 on (
  t1.param_id = t2.param_id and t2.snapshot_id=2)
where t1.snapshot_id=1

(5) By PazO (JohnSmith) on 2021-05-26 06:32:14 in reply to 2 [link] [source]

Thank you very much

This is working

(3) By Rado (antlor) on 2021-05-25 14:43:27 in reply to 1 [link] [source]

select a.snapshot_id, a.param_id, a.param_value
from SnapshotParameters a
where not exists(select 1 from SnapshotParameters b where b.param_id = a.param_id and b.snapshot_id = 2)
  and a.snapshot_id = 1
union all
select a.snapshot_id, a.param_id, a.param_value
from SnapshotParameters a
where a.snapshot_id = 2
order by 2

(4) By David Raymond (dvdraymond) on 2021-05-25 15:01:27 in reply to 1 [source]

If the real schema is as simple as the example you could use SQLite's bare columns in aggregates to get the highest snapshot for each parameter.

Something like... select max(snapshot_id) as snapshot_id, param_id, param_value from SnapshotParameters group by param_id order by param_id;