Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2003
    Location
    Atlanta
    Posts
    134

    Angry Unanswered: Oracle - SQL Query

    Can someone please help to write a query for:

    I have two tables: Vistits and Alerts with FK relationship between those two tables on columns ( ID and visitnumber ) in both tables. I suspect that data integrity issue cropped up. How do I write query to select the records that violate the FK constraint. That means the records that are in "visits" table and not in "alerts" table on match ( ID and visitnumber)?

    Thanks, Vinnie

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    use

    MINUS

    or use

    WHERE ... NOT IN...
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2003
    Location
    Atlanta
    Posts
    134

    Reply

    Please let me know the detail script. Is it like:

    select * from alert a
    where a.id not in (select id from visit b where a.visitnumber=b.visitnumber)

    or something else.

    Excuse me for asking more details.

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    either

    Code:
    select * from alert a 
    where a.id not in (select visitnumber from visit)
    or

    Code:
    select * from alert a 
    where not exists (select 1 from visit b where a.id=b.visitnumber)
    -cf

  5. #5
    Join Date
    Jan 2003
    Location
    Atlanta
    Posts
    134

    Thanks but

    The FK is combination of ID and visitnumber in both tables as said in query request. Does your SQL statement valid in that case as it is trying to match different columns?

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    Code:
    select * from alert a 
    where (a.id, a.visitnumber) not in (select id,visitnumber from visit)
    or

    Code:
    select * from alert a 
    where not exists (select 1 from visit b where a.id=b.id and a.visitnumber=b.visitnumber)
    -cf

  7. #7
    Join Date
    Jan 2003
    Location
    Atlanta
    Posts
    134

    Thanks

    Many thanks

  8. #8
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    I still prefer MINUS as suggested earlier:

    Code:
    SELECT id, visitnumber FROM alert
    MINUS
    SELECT id, visitnumber FROM visit;

  9. #9
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    You can also do it with an outer join
    Code:
    select * from alert a , visit b
    where a.id=b.id(+) and a.visitnumber=b.visitnumber(+)
    and b.id is null
    However the query YOU should use if your going to run this regularly is the one which runs best in your environment. Dont forget the optimizer can rewrite any of the queries given into any of the others in terms of execution plans.

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •