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

Posted on 2012-08-13
Last Modified: 2012-08-21
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:


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

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.


Question by:mgferg
    LVL 34

    Expert Comment

    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 Comment


    Thanks, for your response.

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

    LVL 34

    Accepted Solution

    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;
    LVL 31

    Expert Comment

    select condition, colx, coly, colz from tablea where condition = 'a'
    select 'a', colx, coly, colz from tableb
    order by 1;
    LVL 31

    Expert Comment

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

    Author Closing Comment

    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.
    LVL 34

    Expert Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    Title # Comments Views Activity
    SQL help 5 42
    how to properly combine two queries 6 52
    Simple IF evaluation mistake in SQL Azure statement 10 25
    query Syntax 4 21
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
    This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now