We help IT Professionals succeed at work.

Help with Oracle query - to find a set of rows that don't meet specific conditions across tables

mgferg
mgferg asked
on
Hi all,


I have two tables, say tableA and tableB (tableB is via a dblink on a different server).

first we want to restrict our query to those rows on tableA that have condition='a'

then from this list, find the rows that DO NOT meet the following:
tableA,colx=tableB,colx
tableA,coly=tableB,coly
tableA,colz=tableB,colz

eg.

tableA
condition  colx coly colz
a                1      2      3
a                4      5      6
b                7      8      9
c               10   11     12
a               13   14     15

tableB
colx coly colz
1      2      3
4      5      X
7      X      9
X     11    12
13    X      15


so the rows we want are the ones on tableA has 4 5 6 and 13 14 15  because colz on tableA does not match colz on tableB

We don't care about the mismatches  7 X 9 and X 11 12 because tableA does not have an 'a' in the condition column.

Any help would be much appreciated (and I can elaborate on this if required - I've tried to keep it simple to demonstrate the principle of what I'm trying to do)

DB is Oracle10G and I'm using sql developer to run any queries.

Thanks,

Mark
Comment
Watch Question

johnsoneSenior Oracle DBA
BRONZE EXPERT

Commented:
Why doesn't the 1 2 3 row get selected?  That has a match in tableb.

Try this:


select * from tablea a, tableb b
where a.condition = 'a' and
(a.colx = b.colx or b.colx = 'X') and
(a.coly = b.coly or b.coly = 'X') and
(a.colz = b.colz or b.colz = 'X');


But that will give you the 1 2 3 row.

Author

Commented:
Hi,

Thanks, for your response.

To answer your question - because we want to find the rows that DO NOT meet all three conditions above.

Thanks
Senior Oracle DBA
BRONZE EXPERT
Commented:
Sorry, I misunderstood it.

This should do it:


select * from tablea a left outer join tableb b
on a.colx = b.colx and a.coly = b.coly and a.colz = b.colz
where a.condition = 'a' and
b.colx is null;
awking00Information Technology Specialist
BRONZE EXPERT

Commented:
select condition, colx, coly, colz from tablea where condition = 'a'
minus
select 'a', colx, coly, colz from tableb
order by 1;
awking00Information Technology Specialist
BRONZE EXPERT

Commented:
The minus query assumes the data types are the same in both tables.

Author

Commented:
Thanks for this. Been wanting to research more what a left outer join actually means, but have not had chance to do so. It did however achieve what I was looking for.

If you have a moment, would appreciate some comments on this, specifically with regards to example data above.

Thanks awking00 for your comment. What I forgot to mention is that the table structures are different.
johnsoneSenior Oracle DBA
BRONZE EXPERT

Commented:
The left outer join in this case, says give me all the rows from table a that match the where clause regardless of whether there is a match in tableb.  The is null condition on the column in tableb would indicate that there is no record in tableb that matched the join conditions and those are the ones that you want.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.