Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2005
    Posts
    220

    Unanswered: Query help needed

    Using the first table below, can anyone tell me how I can query it so I can return every possible combination as in the 2nd table?

    Code:
    SKU.ITEM     SKU.LOC
    
    12345        LOC1
    12345        LOC2
    12345        LOC3
    
    Create this...
    
    ITEM      SRCLOC      DESTLOC
    12345     LOC1        LOC2
    12345     LOC1        LOC3
    
    12345     LOC2        LOC1
    12345     LOC2        LOC3
    
    12345     LOC3        LOC1
    12345     LOC3        LOC2

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    Code:
    SQL> select a.item, a.loc as srcloc, b.loc destloc
      2  from sku a, sku b
      3  where a.item = b.item and
      4        a.loc != b.loc
      5  order by 1,2,3;
    
    ITEM       SRCLOC     DESTLOC
    ---------- ---------- ----------
    12345      LOC1       LOC2
    12345      LOC1       LOC3
    12345      LOC2       LOC1
    12345      LOC2       LOC3
    12345      LOC3       LOC1
    12345      LOC3       LOC2
    
    6 rows selected.
    ---=cf

  3. #3
    Join Date
    Jan 2005
    Location
    Atlanta
    Posts
    58
    maybe ...

    select i.item, s.loc, d.loc
    from SKU.ITEM i
    cross join SKU.LOC s
    cross join SKU.LOC d
    where s.loc != d.loc

    It may help to send the DDL for the tables.

  4. #4
    Join Date
    Jan 2005
    Location
    Atlanta
    Posts
    58
    I am assuming that SKU is a schema. Correct?

  5. #5
    Join Date
    Sep 2005
    Posts
    220
    Bingo, that did it. 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
  •