Matt Matt - 5 months ago 16
SQL Question

Oracle SQL - Using the results from two queries as the WHERE condition in another

I have two queries.

Query 1:

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'


Query 2:

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'


Query 1 Output:

ccc nnn ttt aaa
07 1369 F 3400
07 1369 F 3402
19 1369 F 3601
19 1369 F 3600
20 1369 F 3500
20 1369 F 3500
20 1369 F 3500
20 1369 F 3500
20 1369 F 3500
20 1369 F 3500
20 1369 F 3500
20 1369 F 3500
20 1369 F 3500
20 1369 F 3500
20 1369 F 3500


Query 2 Output:

ccc nnn ttt aaa
19 1369 F 1564


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.

Expected Output:

ccc nnn ttt aaa
19 1369 F 3601
19 1369 F 3600
19 1369 F 1564

Gar Gar
Answer

I came up with this, seem complicated, might be a bit optimized

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'
Comments