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,
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 )
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.