What I want to get is using the results of these two tables to get an output where when a ccc has results in both queries all of the returned data from both queries are output, if a ccc has results in only one query, or in none, then none of its results are output.

3 Answers
3

SELECT g.ccc, g.nnn, g.ttt, g.aaa
FROM table1 g,
(
SELECT distinct g.ccc
FROM table1 g
WHERE (g.aaa BETWEEN '1544' AND '1567'
or g.aaa BETWEEN '3000' AND '3999')
AND g.ccc NOT IN('05','06')
AND g.nnn = '1369'
) t.ccc
WHERE g.aaa BETWEEN '1544' AND '1567'
AND g.ccc=t.ccc
AND g.nnn = '1369'
union
SELECT g.ccc, g.nnn, g.ttt, g.aaa
FROM table1 g,
(
SELECT distinct g.ccc
FROM table1 g
WHERE (g.aaa BETWEEN '1544' AND '1567'
or g.aaa BETWEEN '3000' AND '3999')
AND g.ccc NOT IN('05','06')
AND g.nnn = '1369'
) t.ccc
WHERE g.aaa BETWEEN '3000' AND '3999'
AND g.ccc=t.ccc
AND g.nnn = '1369'

WITH query1 AS (
SELECT g.ccc, g.nnn, g.ttt, g.aaa
FROM table1 g
WHERE g.aaa BETWEEN '3000' AND '3999'
AND g.ccc NOT IN('05','06')
AND g.nnn = '1369'
),
query2 AS (
SELECT g.ccc, g.nnn, g.ttt, g.aaa
FROM table1 g
WHERE g.aaa BETWEEN '1544' AND '1567'
AND g.ccc NOT IN('05','06')
AND g.nnn = '1369'
)
SELECT * FROM query1
WHERE ccc IN ( SELECT ccc FROM query2 )
UNION ALL
SELECT * FROM query2
WHERE ccc IN ( SELECT ccc FROM query1 )

We can use the analytic functions MAX() OVER (), with partition by ccc

SELECT *
FROM (SELECT x.*,
MAX (CASE WHEN (g.aaa BETWEEN '3000' AND '3999' AND g.ccc NOT IN ('05', '06') AND g.nnn = '1369') THEN 1 ELSE 0 END) OVER (PARTITION BY ccc) c1,
MAX (CASE WHEN (g.aaa BETWEEN '1544' AND '1567' AND g.ccc NOT IN ('05', '06') AND g.nnn = '1369') THEN 1 ELSE 0 END) OVER (PARTITION BY ccc) c2
FROM table1 g)
WHERE c1 = 1 AND c2 = 1