eugenn eugenn - 3 months ago 13
SQL Question

How two merge two queries into one result set without duplicates?

I have a table with three columns id, name, training_run_id, named_entity_id.
I would like to make result set with two name columns but with different type_id

SELECT r1.id, r1.f1, r2.id, r2.f1 FROM
(
SELECT id, f1 from RESULT where TRAINING_RUN_ID IN (1,2) AND NAMED_ENTITY_ID = 13
GROUP BY ID
) as r1
JOIN
(
SELECT id, f1 from RESULT where TRAINING_RUN_ID IN (3,4) AND NAMED_ENTITY_ID = 13
GROUP BY ID
) as r2


Result of the query is:

id f1 training_run_id id f1 training_run_id
27 0.3 2 41 0.5 3
27 0.3 2 55 0.66 4
13 0.2 1 41 0.5 3
13 0.2 1 55 0.66 4


I am wondering how to get this one:

id f1 training_run_id id f1 training_run_id
13 0.2 1 41 0.5 3
27 0.3 2 55 0.66 4

Answer

A couple of assumptions (based on OP's comments):

  • Your 2 subqueries always have matching row counts
  • How the rows are matched is unimportant

If the above is true, I think you can simply have each subquery return rownum so that you can join on that to avoid a cartesian plan:

SELECT r1.id, r1.f1, r2.id, r2.f1 FROM
(
    SELECT rownum as rn, id, f1  from RESULT where TRAINING_RUN_ID IN (1,2) AND NAMED_ENTITY_ID = 13
    GROUP BY ID
) as r1
JOIN
(
    SELECT rownum as rn, id, f1 from RESULT where TRAINING_RUN_ID IN (3,4) AND NAMED_ENTITY_ID = 13
    GROUP BY ID
) as r2
ON r1.rn = r2.rn

EDIT

Actually, not too sure if the above will work correctly, because as specified in the documentation about the rownum function, it seems like rownum gets computed before the group by.

To get the row number after ordering and grouping, use a subquery.

If that's the case, I think this is the correct query:

SELECT r1.id, r1.f1, r2.id, r2.f1 FROM
(
  select rownum() as rn, id, f1
  from (
    SELECT id, f1  from RESULT where TRAINING_RUN_ID IN (1,2) AND NAMED_ENTITY_ID = 13
    GROUP BY ID
  ) t
) as r1
JOIN
(
  select rownum() as rn, id, f1
  from (
    SELECT id, f1 from RESULT where TRAINING_RUN_ID IN (3,4) AND NAMED_ENTITY_ID = 13
    GROUP BY ID
  ) t
) as r2
ON r1.rn = r2.rn
Comments