[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 688
  • Last Modified:

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

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
0
mgferg
Asked:
mgferg
  • 3
  • 2
  • 2
1 Solution
 
johnsoneSenior Oracle DBACommented:
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.
0
 
mgfergAuthor 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
0
 
johnsoneSenior Oracle DBACommented:
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;
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
awking00Commented:
select condition, colx, coly, colz from tablea where condition = 'a'
minus
select 'a', colx, coly, colz from tableb
order by 1;
0
 
awking00Commented:
The minus query assumes the data types are the same in both tables.
0
 
mgfergAuthor 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.
0
 
johnsoneSenior Oracle DBACommented:
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.
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now