DB2- fetching records having products in more than one store

Question

I have a table having records as below

store num product no.

0001 11

0002 11

0003 11

0001 12

0002 12

0001 13

I want to fetch records having products in more than one store. The result should be like below

store num product no.

0001 11

0002 11

0003 11

0001 12

0002 12

The last record should not be there since product is in only one store.

Please help?

Answer 1

I'm going to call your table 'availability'. I did the following with PostgreSQL, but it's standard SQL, and i believe DB2 has excellent support for standard SQL, and will handle these just fine.

Here's the way that feels most natural to me:

select *
from availability
where product_no in (
    select product_no
    from availability
    group by product_no
    having count(*) > 1
);

If you'd prefer that as a correlated subquery:

select *
from availability a
where (
    select count(*)
    from availability
    where product_no = a.product_no
) > 1;