Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2011
    Posts
    2

    Unanswered: Duplicates in Same Column/Table, Different Where-Clauses

    I may have overcomplicated this task, Iíve tried different approaches to filter the results, but my query still gives more than I asked for. Iím looking for duplicate values (vendor) on 2 tables, using different where-clauses. I want to know if the same vendor exists in 2 different departments of the same company.

    The query thatís gotten me closest is this:

    SELECT distinct a.vendor, c.dept,
    COUNT(a.vendor) as times_dupl
    FROM VendorTbl a,
    DptTbl b,
    DescTbl c
    where (c.dept = 123 or
    c.dept = 456)
    and a.vendor = b.vendor
    and b.desc = c.desc
    GROUP BY a.vendor, c.dept
    HAVING ( COUNT(a.vendor) > 1 )
    with ur;


    I started out with an implict JOIN, then UNIONed 2 different SELECTs with where clauses having the different department numbers (123 and 456), then pared it down to the above single SELECT statement. But it still produces more results than just a single occurrence of the same vendor in both departments 123 and 456. What am I missing? Thanks for looking.

  2. #2
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    maybe you have to add something like b.dept = c.dept ....

    Who knows structure of your tables ?

  3. #3
    Join Date
    Oct 2011
    Posts
    2
    Quote Originally Posted by Lenny77 View Post
    maybe you have to add something like b.dept = c.dept
    Good suggestion. I'll add more like columns to where-clause to winnow results down and post query that gives correct results. Thanks.

Tags for this Thread

Posting Permissions

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