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.

maybe you have to add something like b.dept = c.dept ....

Who knows structure of your tables ?

Good suggestion. I'll add more like columns to where-clause to winnow results down and post query that gives correct results. Thanks.