I have a single table named Alias ( for Bar code Aliases ).

My Alias table has 2 fields PartNumber and VendorNumber.

As a sample - lets say the data is :

p# v#
1 | A
2 | B
3 | C
4 | D
5 | E
6 | F
7 | A
8 | C
9 | G

What I want is a sql that will result in showing me the part numbers that have duplicate vendor numbers - and only for the duplicate vendors numbers.

Based on the sample data - what I would love to see is a result of

A | 1 | 7
C | 3 | 8

now - if I got results of
A | 1 | 7
A | 7 | 1
C | 3 | 8
C | 8 | 3

that would be even better

I've tried multiple ways and have failed to get this working -
any ideas on how to write this one?!?

Thanks in advance!

Chris

Maybe, I am missing something, but this should work.

Dave

The part numbers (p#) in your sample data appear pretty unique to me

and how would you show things if there were 3 vendors supplying the same part?

I'm confused - why would this be better?

Something a bit like the following:

Code:

select part, group_concat( vendor )
from Alias
where part in (
    select part
    from Alias
    group by part
    having count(*) > 1 )
group by part

Dave -

Thanks - your results were perfect.

I don't know why I didn't think of inner joining the table again - i've done that before - I guess its one of those little things that escaped me!

but again - Thanks!!