Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2010
    Posts
    16

    Unanswered: This one has stumped me

    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

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Maybe, I am missing something, but this should work.

    Code:
    select a.vendor, a.part_nbr, b.part_nbr
       from your_table a
    inner join your_table b  -- keep in mind this is same table
       on a.vendor = b.vendor
      and a.part_nbr <> b.part_nbr
    Dave

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by dockraz View Post
    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
    The part numbers (p#) in your sample data appear pretty unique to me

    Quote Originally Posted by dockraz View Post
    Based on the sample data - what I would love to see is a result of

    A | 1 | 7
    C | 3 | 8
    and how would you show things if there were 3 vendors supplying the same part?

    Quote Originally Posted by dockraz View Post
    now - if I got results of
    A | 1 | 7
    A | 7 | 1
    C | 3 | 8
    C | 8 | 3

    that would be even better
    I'm confused - why would this be better?

    Quote Originally Posted by dockraz View Post
    any ideas on how to write this one?!?
    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

  4. #4
    Join Date
    Mar 2010
    Posts
    16
    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!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •