SQL Query Find Items With Two Criteria

Question

I may be over thinking but this has me stumped, I have a table with items and want to display only items that appear more than once. For example, item 14295 is in the table twice once in company 1 and again in company 2. The field are FFIITMN item, FFIIDE1 description and FFICMPN company. I want to identify these so that we can have someone clean up this table. I have tried the following:

SELECT FFIITMN, FFIIDE1, FFICMPN
FROM S2151BDW.PWRDTA.FFIITMAP
WHERE FFICMPN = '  1' AND FFICMPN = '  2'
ORDER BY FFIITMN

Answer 1

If you want to see all items present in multiple companies (or in one but multiple times), you could use something like this:

select
    itm.*
FROM
    S2151BDW.PWRDTA.FFIITMAP itm
    join (
        SELECT
            FFIITMN,
            count(*)
        FROM
            S2151BDW.PWRDTA.FFIITMAP
        group by
            FFIITMN            
        Having
            count(*) > 1
    ) co on itm.FFIITMN = co.FFIITMN
    ORDER BY itm.FFIITMN, itm.FFICMPN

Here inner query selects all items that are multiple, and outer query selects this items with respective companies.