Professional Web Applications Themes

Funny join - Microsoft SQL / MS SQL Server

Let's say I have three tables: Table1 ------ ID Field Table2 ------ ID Field Table3 ------ ID Field ID is the primary key of each of these tables, and Field is just some arbitrary field specific to that table. The same ID may be present in more than one table, or it may not. I would like to write a query that returns the following: Query ----- ID Table1_Field Table2_Field Table3_Field So, if the same ID is present in all three tables, then Table1_Field, Table2_Field, and Table3_Field should all have values. Otherwise, the ones that are missing should just be ...

Sponsored Links
  1. #1

    Default Funny join

    Let's say I have three tables:

    Table1
    ------
    ID
    Field

    Table2
    ------
    ID
    Field

    Table3
    ------
    ID
    Field

    ID is the primary key of each of these tables, and Field
    is just some arbitrary field specific to that table. The
    same ID may be present in more than one table, or it may
    not. I would like to write a query that returns the
    following:

    Query
    -----
    ID
    Table1_Field
    Table2_Field
    Table3_Field

    So, if the same ID is present in all three tables, then
    Table1_Field, Table2_Field, and Table3_Field should all
    have values. Otherwise, the ones that are missing should
    just be empty.

    How can I do this?
    Sponsored Links
    Michael Guest

  2. #2

    Default Re: Funny join

    Michael,
     

    CREATE TABLE Table1 (
    ID INT NOT NULL PRIMARY KEY,
    Field CHAR(1) NOT NULL);

    CREATE TABLE Table2 (
    ID INT NOT NULL PRIMARY KEY,
    Field CHAR(1) NOT NULL);

    CREATE TABLE Table3 (
    ID INT NOT NULL PRIMARY KEY,
    Field CHAR(1) NOT NULL);

    INSERT INTO Table1 VALUES (1,'A');
    INSERT INTO Table2 VALUES (2,'B');
    INSERT INTO Table3 VALUES (3,'C');
    INSERT INTO Table1 VALUES (4,'X');
    INSERT INTO Table2 VALUES (4,'X');
    INSERT INTO Table2 VALUES (5,'Y');
    INSERT INTO Table3 VALUES (5,'Y');
    INSERT INTO Table1 VALUES (6,'Z');
    INSERT INTO Table2 VALUES (6,'Z');
    INSERT INTO Table3 VALUES (6,'Z');

    SELECT
    COALESCE(t1.ID, t2.ID, t3.ID) AS cid,
    t1.Field AS f1,
    t2.Field AS f2,
    t3.Field AS f3
    FROM
    (Table1 AS t1 FULL OUTER JOIN
    Table2 AS t2 ON t2.ID=t1.ID) FULL OUTER JOIN
    Table3 AS t3 ON t3.ID=COALESCE(t1.ID,t2.ID)
    ORDER BY cid, f1, f2, f3;


    Hope that helps,
    Rich


    Rich Guest

  3. #3

    Default Re: Funny join

    Thanks. That works.
    Michael Guest

Similar Threads

  1. Is left-join faster then inner join?
    By howachen@gmail.com in forum MySQL
    Replies: 5
    Last Post: March 5th, 07:11 AM
  2. Replies: 2
    Last Post: September 18th, 09:59 PM
  3. FAQ - Funny
    By benreilly webforumsuser@macromedia.com in forum Macromedia Freehand
    Replies: 2
    Last Post: September 15th, 03:32 PM
  4. Rewrite Query using Inner Join, Left Join e.t.c
    By Vishal in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 23rd, 08:02 AM
  5. Select Left Join AND Right Join
    By David in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 15th, 03:42 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