Require report in SQL, cannot get head around it! 1

[MCubitt]

I have a table which has
Item Number (ITEM)
Site (SITE)
and code (CODE)

There can be more than one SITE per ITEM but only one CODE per ITEM/SITE. EG:

ITEM SITE CODE
1 A 03
2 A 03
2 B 03
3 A 03
3 B 04

I want to report where the code is different for the same ITEM no matter what site, eg:

ITEM
3

Help, please!





Applications Support
UK

 

[carp]

Try

SELECT item, code
FROM my_table
GROUP BY item, code
HAVING count(*) > 1;

 

[MCubitt]

Close, but I would like to report each instance of the error:

In my above example I would see
3 A 03
3 B 04

Thanks


Applications Support
UK

 

[MCubitt]

In fact, is the query you provided actually listing each ITEM and CODE where it occurs more than once? This is not my requirement. I want to report where for the same ITEM, no matter what SITE, there is more than one different CODE, ie above there are two CODES for ITEM 3.






Applications Support
UK

 

[LKBrwnDBA]

Try something like:

select item from (
     select distinct item, code 
       from itemtab)
 group by item
having count(*) > 1

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

Ooops, posted too late!


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[carp]

Not at all! I spat my solution out to fast - then realized it was completely wrong while in a meeting I had to go to.
Thank you, LKBrwnDBA for providing the RIGHT answer!

 

[MCubitt]

LK, that's closer, has all the right ITEMS.

Thanks


Applications Support
UK

 

[MCubitt]

Thanks guys



Applications Support
UK

 

[MCubitt]

This seems to do it:
select ITEM, SITE, CODE from ITEM_TABLE where ITEM in(
select
ITEM
from (
select distinct ITEM, CODE
from ITEM_TABLE)
group by
ITEM
having
count(*) > 1)
order by
1


Might not be efficient but works!


Applications Support
UK