Query two query results 1

[Sandman83]

Hi All,

I'm pretty sure what I'm looking to do is not too complicated, but can't figure out how to write the query. I'm trying to get all records from one table that does not show up in two other tables. I currently have two queries similar to the following:

SELECT tableA.item1, COUNT(tableB.item1)
FROM tableA LEFT OUTER JOIN tableB
ON tableB.item1 = tableA.item1
HAVING COUNT(tableB.item1) < 1

And

SELECT tableA.item1, COUNT(tableC.item1)
FROM tableA LEFT OUTER JOIN tableC
ON tableC.item1 = tableA.item1
HAVING COUNT(tableC.item1) < 1

Is there a way to combine theses into one query, or am I going about the wrong way? Any help is greatly appreciated.

Tim

 

[SantaMufasa]

I'm trying to get all records from one table that does not show up in two other tables.

If the three tables have identical structures, and you are trying to identify rows in TableA that do not appear in either TableB or TableC, then the quickest simplest method of doing so is using Oracle's SET operators:

SELECT * FROM TableA
MINUS
(SELECT * FROM TableB
 UNION
 SELECT * FROM TableC);

Let us know if resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Sandman83]

Thanks for the reply Mufasa. Unfortunately all three tables structures are different. Could I do something similar to your code since the two fields I want to return are identical?

 

[SantaMufasa]

Yes...as long as you structure your queries so that the number of columns in the queries and the data types of those columns are identical, that's all that matters...you can structure your queries any way you want.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Sandman83]

Thanks for your help Mufasa. The following query works perfectly.

SELECT msg_num, msg_id
FROM announce
MINUS
(SELECT ann.msg_num, ann.msg_id
FROM announce ann LEFT OUTER JOIN dnis d
ON d.ct_ann_num = ann.msg_num
HAVING COUNT(d.ct_ann_num) > 0
GROUP BY ann.msg_num, ann.msg_id
UNION
SELECT ann.msg_num, ann.msg_id
FROM announce ann LEFT OUTER JOIN cctvariable cct
ON cct.value = ann.msg_num AND cct.type = 1 and cct.major_version = 0
HAVING COUNT(cct.value) > 0
GROUP BY ann.msg_num, ann.msg_id)

 

[SantaMufasa]

Yes, Sandman, the code works perfectly, but it is doing more work than it needs to do...You don't need to do the LEFT OUTER JOINS. Simple (non-LEFT-OUTER) joins give what you want. First, here are some sample data for your tables:

SQL> select * from announce;

   MSG_NUM     MSG_ID
---------- ----------
         1          1
         2          2
         3          3
         4          4
         5          5
         6          6

6 rows selected.

SQL> select * from dnis;

CT_ANN_NUM
----------
         1
         3

2 rows selected.

SQL> select * from cctvariable;

     VALUE       TYPE MAJOR_VERSION
---------- ---------- -------------
         2          2             2
         5          1             0
         4          4             4

3 rows selected.

With the above data, the results should be ANNOUNCE rows 2, 4, and 6. Here is your code from above, simplified (and performance optimized) using simple joins:

select msg_num, msg_id
  from announce ann
minus
(select msg_num, msg_id
   from announce, dnis
  where ct_ann_num = msg_num
  union
 select msg_num, msg_id
   from announce, cctvariable
  where value = msg_num and type = 1 and major_version = 0);

   MSG_NUM     MSG_ID
---------- ----------
         2          2
         4          4
         6          6

3 rows selected.

Let us know if you have questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Dagon]

Although the MINUS technique is very good, you could still have used outer joins since there is nothing to stop you outer joining to 2 different tables. I would suggest the following:

select a.item1, 
       case when b.item1 is null then 'Y' else 'N' end b_missing, 
       case when c.item1 is null then 'Y' else 'N' end c_missing
from tableA a left join tableB b
on a.item1 = b.item1
left join tableC c on a.item1 =c.item1
where (b.item1 is null or c.item1 is null)
order by a.item1