Results 1 to 2 of 2

Thread: Basic question

  1. #1
    Join Date
    Jan 2003
    Location
    Harpenden, England
    Posts
    1

    Unhappy Unanswered: Basic question

    I have two fields in a table, customer and reference.
    A problem has occured that means the same references have been assigned to different customers in errors.
    Ignoring the fact that constraints could have prevented this, how would I identify the rows where more than one customer is attached to the same reference.

    The current SQL I am using is:

    select customer,reference
    from table
    where reference in
    (select reference from
    (select reference,count(*) from
    table
    group by reference
    having count(*) > 1))

    This appears to work but I suspect there is a simpler method - any ideas as this can take a long time?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Basic question

    You don't need so many subqueries:

    select customer,reference
    from table
    where reference in
    (select reference from table
    group by reference
    having count(*) > 1)

    I don't think there is a quicker way than that, assuming there is an index on table.reference (if not add maybe one first).

Posting Permissions

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