Return result of several set operations on subqueries - Mysql

serviola_29
February 19, 2023
219 views
2 votes
3 Answers

I am using MySql and running the following queries.

(select CONCAT(RESOURCE, " AMB:", AMB) AS REC_1 from db.CRE
 where CONTENT = '183' and LEVEL = '99'
 EXCEPT
 select CONCAT(RESOURCE, " AMB:", AMB) AS REC_1 from db.CRE
 where CONTENT = '182' and LEVEL = '99')

(select CONCAT(RESOURCE, " AMB:", AMB) AS REC_SAME from db.CRE
 where CONTENT = '183' and LEVEL = '99'
INTERSECT
select CONCAT(RESOURCE, " AMB:", AMB) AS REC_SAME from db.CRE
where CONTENT = '182' and LEVEL = '99')

(select CONCAT(RESOURCE, " AMB:", AMB) AS REC_2 from db.CRE
 where CONTENT = '182' and LEVEL = '99'
 EXCEPT
 select CONCAT(RESOURCE, " AMB:", AMB) AS REC_2 from db.CRE
 where CONTENT = '183' and LEVEL = '99')

The subqueries are always the same but the set operation changes. All of these queries work on their own. The first tells me the feature unique to subquery a the second what both share and the third what is unique to subquery b. The issue is that I would like to get all three of these returned at the same time. One column being REC_1 another REC_SAME another REC_2.

I have tried using UNIONS to join the but this fails if any of them are null. I have also tried using select without a from but it also didn’t work.

The results i would expect to be with the following structure.In which rec1 would contain the results of the first query , rec_same of the intersect query and rec_2 of the last query.

Rec1|Rec_same|Rec2

Tags: mysql sql

Answers

AS MySQL doesn’t support FULL OUTER JOIN you have to build it by hand

    SELECT
        REC_1,REC_SAME,REC_2 
    FROM
        (SELECT
            REC_1,REC_SAME
        FROM
        ((select CONCAT(RESOURCE, " AMB:", AMB) AS REC_1 from CRE
         where CONTENT = '183' and `LEVEL` = '99'
         EXCEPT
         select CONCAT(RESOURCE, " AMB:", AMB) AS REC_1 from CRE
         where CONTENT = '182' and `LEVEL` = '99')) a1
         LEFT JOIN
         ((select CONCAT(RESOURCE, " AMB:", AMB) AS REC_SAME from CRE
         where CONTENT = '183' and `LEVEL` = '99'
        INTERSECT
        select CONCAT(RESOURCE, " AMB:", AMB) AS REC_SAME from CRE
        where CONTENT = '182' and `LEVEL` = '99')) a2 ON a1.REC_1 = a2.REC_SAME
        UNION
        SELECT
            REC_1,REC_SAME
        FROM
        (((select CONCAT(RESOURCE, " AMB:", AMB) AS REC_1 from CRE
         where CONTENT = '183' and `LEVEL` = '99'
         EXCEPT
         select CONCAT(RESOURCE, " AMB:", AMB) AS REC_1 from CRE
         where CONTENT = '182' and `LEVEL` = '99')) a1
         RIGHT JOIN
         ((select CONCAT(RESOURCE, " AMB:", AMB) AS REC_SAME from CRE
         where CONTENT = '183' and `LEVEL` = '99'
        INTERSECT
        select CONCAT(RESOURCE, " AMB:", AMB) AS REC_SAME from CRE
        where CONTENT = '182' and `LEVEL` = '99')) a2 ON a1.REC_1 = a2.REC_SAME) )  b1
        LEFT JOIN
        (select CONCAT(RESOURCE, " AMB:", AMB) AS REC_2 from CRE
     where CONTENT = '182' and LEVEL = '99'
     EXCEPT
     select CONCAT(RESOURCE, " AMB:", AMB) AS REC_2 from CRE
     where CONTENT = '183' and LEVEL = '99') b2 ON b1.REC_1 = b2.REC_2  OR b1.REC_SAME = b2.REC_2 
 UNION
     SELECT
        REC_1,REC_SAME,REC_2 
    FROM
        (SELECT
            REC_1,REC_SAME
        FROM
        ((select CONCAT(RESOURCE, " AMB:", AMB) AS REC_1 from CRE
         where CONTENT = '183' and `LEVEL` = '99'
         EXCEPT
         select CONCAT(RESOURCE, " AMB:", AMB) AS REC_1 from CRE
         where CONTENT = '182' and `LEVEL` = '99')) a1
         LEFT JOIN
         ((select CONCAT(RESOURCE, " AMB:", AMB) AS REC_SAME from CRE
         where CONTENT = '183' and `LEVEL` = '99'
        INTERSECT
        select CONCAT(RESOURCE, " AMB:", AMB) AS REC_SAME from CRE
        where CONTENT = '182' and `LEVEL` = '99')) a2 ON a1.REC_1 = a2.REC_SAME
        UNION
        SELECT
            REC_1,REC_SAME
        FROM
        (((select CONCAT(RESOURCE, " AMB:", AMB) AS REC_1 from CRE
         where CONTENT = '183' and `LEVEL` = '99'
         EXCEPT
         select CONCAT(RESOURCE, " AMB:", AMB) AS REC_1 from CRE
         where CONTENT = '182' and `LEVEL` = '99')) a1
         RIGHT JOIN
         ((select CONCAT(RESOURCE, " AMB:", AMB) AS REC_SAME from CRE
         where CONTENT = '183' and `LEVEL` = '99'
        INTERSECT
        select CONCAT(RESOURCE, " AMB:", AMB) AS REC_SAME from CRE
        where CONTENT = '182' and `LEVEL` = '99')) a2 ON a1.REC_1 = a2.REC_SAME) )  b1
        RIGHT JOIN
        (select CONCAT(RESOURCE, " AMB:", AMB) AS REC_2 from CRE
         where CONTENT = '182' and LEVEL = '99'
         EXCEPT
         select CONCAT(RESOURCE, " AMB:", AMB) AS REC_2 from CRE
         where CONTENT = '183' and LEVEL = '99') b2 ON b1.REC_1 = b2.REC_2  OR b1.REC_SAME = b2.REC_2

You can add rec_type(‘rec_1’, ‘rec_same’,’rec_2′) columns in all 3 subqueries, and then perform a union. At the end, use group by and sum function to exact which rec is rec_1/rec_same/rec_2. See the example query:

WITH rec_1 as(
(select CONCAT(RESOURCE, " AMB:", AMB) AS REC, 'rec_1' as rec_type from db.CRE
 where CONTENT = '183' and LEVEL = '99'
 EXCEPT
 select CONCAT(RESOURCE, " AMB:", AMB) AS REC, 'rec_1' as rec_type from db.CRE
 where CONTENT = '182' and LEVEL = '99')
),
rec_same AS (
(select CONCAT(RESOURCE, " AMB:", AMB) AS REC, 'rec_same' as rec_type from db.CRE
 where CONTENT = '183' and LEVEL = '99'
INTERSECT
select CONCAT(RESOURCE, " AMB:", AMB) AS REC,'rec_same' as rec_type from db.CRE
where CONTENT = '182' and LEVEL = '99')
),
rec_2 AS (
(select CONCAT(RESOURCE, " AMB:", AMB) AS REC,'rec_2' as rec_type from db.CRE
 where CONTENT = '182' and LEVEL = '99'
 EXCEPT
 select CONCAT(RESOURCE, " AMB:", AMB) AS REC, 'rec_2' as rec_type from db.CRE
 where CONTENT = '183' and LEVEL = '99')
),
rec_union AS(
select
    REC, rec_type
FROM
    rec_1
UNION
select
    REC, rec_type
FROM
    rec_same
UNION
select
    REC, rec_type
FROM
    rec_2
)
select
   rec,
   sum(rec_type = 'rec_1') as is_rec_1,
   sum(rec_type = 'rec_same') as is_rec_same,
   sum(rec_type = 'rec_2') as is_rec_2
from
   rec_union
group by rec

You can do:

with 
a as (select concat(resource, " AMB:", amb) as c from cre where content = '183' and level = '99'),
b as (select concat(resource, " AMB:", amb) as c from cre where content = '182' and level = '99')
select *
from (
  select 'same' as type, a.c from a join b on b.c = a.c
  union all select 'rec1' as type, a.c from a left join b on b.c = a.c where b.c is null
  union all select 'rec2' as type, b.c from b left join a on b.c = a.c where a.c is null
) x;

Result:

 type  c       
 ----- ------- 
 same  3 AMB:4 
 rec1  5 AMB:6 
 rec2  1 AMB:2

See running example at db<>fiddle.

Please signup or login to give your own answer.

Click here to cancel reply.

Return result of several set operations on subqueries – Mysql

Answers