Professional Web Applications Themes

outer join, cross join, union? DDL/sample data incl. - Microsoft SQL / MS SQL Server

Ok, I'm sure i'm missing something obvious... DDL/DML create table #codes (Code char(1)) -- insert distinct codes insert #codes select 'A' insert #codes select 'B' insert #codes select 'C' here is the expected output - the disctinct combination of codes, with their relative positions preserved Code ------ A AB ABC AC B BC C or, alternatively Code1 Code2 Code3 ------- -------- ------- A NULL NULL A B NULL A B C A C NULL B NULL NULL B C NULL C NULL NULL ---------------------------------------------------- The views expressed here are my own and not of my employer. ----------------------------------------------------...

  1. #1

    Default outer join, cross join, union? DDL/sample data incl.

    Ok, I'm sure i'm missing something obvious...

    DDL/DML

    create table #codes (Code char(1))

    -- insert distinct codes
    insert #codes select 'A'
    insert #codes select 'B'
    insert #codes select 'C'


    here is the expected output - the disctinct combination of codes, with their
    relative positions preserved

    Code
    ------
    A
    AB
    ABC
    AC
    B
    BC
    C

    or, alternatively

    Code1 Code2 Code3
    ------- -------- -------
    A NULL NULL
    A B NULL
    A B C
    A C NULL
    B NULL NULL
    B C NULL
    C NULL NULL


    ----------------------------------------------------
    The views expressed here are my own
    and not of my employer.
    ----------------------------------------------------


    Kevin Guest

  2. #2

    Default Re: outer join, cross join, union? DDL/sample data incl.

    SELECT C1.code, C2.code, C3.code
    FROM
    codes AS C1
    LEFT JOIN
    (SELECT code FROM codes
    UNION ALL SELECT NULL) AS C2
    ON (C1.code<C2.code OR C2.code IS NULL)
    LEFT JOIN
    (SELECT code FROM codes
    UNION ALL SELECT NULL) AS C3
    ON (C2.code<C3.code OR C3.code IS NULL)
    ORDER BY C1.code, C2.code, C3.code

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  3. #3

    Default Re: outer join, cross join, union? DDL/sample data incl.

    sweet, thanks


    --
    ----------------------------------------------------
    The views expressed here are my own
    and not of my employer.
    ----------------------------------------------------
    "David Portas" <org> wrote in message
    news:phx.gbl... 


    Kevin Guest

  4. #4

    Default Re: outer join, cross join, union? DDL/sample data incl.

    A T-SQL alternative :

    SELECT col
    FROM (SELECT COALESCE(c1, SPACE(0)) +
    COALESCE(c2, SPACE(0)) +
    COALESCE(c3, SPACE(0))
    FROM ( SELECT 'A', 'B', 'C' ) D ( c1, c2, c3 )
    GROUP BY c1, c2, c3
    WITH CUBE ) D (col)
    WHERE LEN(col ) > 0
    ORDER BY col ;

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

Similar Threads

  1. Oracle 8i Outer Join
    By kodemonki in forum Coldfusion Database Access
    Replies: 10
    Last Post: February 16th, 06:04 PM
  2. Outer join?
    By Ignoramus23298 in forum MySQL
    Replies: 5
    Last Post: May 22nd, 09:06 AM
  3. OUTER JOIN
    By jorgepino in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: May 23rd, 12:59 PM
  4. Replies: 2
    Last Post: September 18th, 09:59 PM
  5. Set Operation or Outer Join?
    By Jack in forum Oracle Server
    Replies: 3
    Last Post: October 20th, 06:14 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139