Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Location
    El Salvador
    Posts
    46

    Unanswered: select from 3 tables

    Hi, I've got a problem... Let's say I have 3 tables each with only one column and the following information:

    TABLE_A
    A
    B
    C

    TABLE_B
    X

    TABLE_C
    G
    H

    I want to make a select of the three tables to have the following result:

    COLUMN_A, COLUMN_B, COLUMN_C
    A, X, G
    B, , H
    C, ,

    If I do the following select:

    Code:
    select isnull(a.column_a, '') as column_a,
    isnull(b.column_b, '') as column_b,
    isnull(c.column_c, '') as column_c
    from
    table_a a, table_b b, table_c c
    I get all the possible combinations as a result.. any ideas?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do this in excel

    this is not a actual database problem, is it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115

    Post

    Quote Originally Posted by diegocro
    I have 3 tables each with only one column
    You should add one more column which will store ID_NO. This way your table looks like this :

    TABLE_A

    1 A
    2 B
    3 C

    TABLE_B

    1 X

    TABLE_C

    1 G
    2 H

    You have plenty of table join options to get the require result set...

  4. #4
    Join Date
    May 2006
    Posts
    11
    i don't know if i've understood ur problem, anyway try to use DISTINCT:
    SELECT DISTINCT isnull(a.column_a, '') as column_a,
    isnull(b.column_b, '') as column_b,
    isnull(c.column_c, '') as column_c
    from
    table_a a, table_b b, table_c c

    it may help

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    holy_joy -- you haven't

    why don't you test your query and see what it produces?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    El Salvador
    Posts
    46
    Quote Originally Posted by rajeshpatel
    You should add one more column which will store ID_NO. This way your table looks like this :

    TABLE_A

    1 A
    2 B
    3 C

    TABLE_B

    1 X

    TABLE_C

    1 G
    2 H

    You have plenty of table join options to get the require result set...

    I tried that but when I do the joins on the table that has the most data, it works fine, but when the join is done on a table that doesn't, I get the following result:


    column_a, column_b, column_c
    A, X, G
    B, ,
    C, ,
    , , H

    or something similar, and since the data is changing on every table, I don't know which one has the most data.

  7. #7
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    This helps you...

    Change the column name as per yours....

    Select ISNULL(a.data, '') as Table_A , ISNULL(b.data, '') as Table_B, ISNULL(c.data, '') as Table_C
    From Table_A a left outer JOIN Table_B b
    ON a.[id] = b.[id] left outer JOIN Table_C c
    ON a.[id] = c.[id]

    I am editing the post because above query will give you all rows which is match with Table_A.

    Instead of left outer join, the full outer join will produce required result set. - check the below query.

    SELECT ISNULL(a.data, '') As Table_A , ISNULL(b.data, '') As Table_B, ISNULL(c.data, '') As Table_C
    FROM Table_A a FULL OUTER JOIN Table_B b
    ON a.[id] = b.[id] FULL OUTER JOIN Table_C c
    ON b.[id] = c.[id]
    Last edited by rajeshpatel; 07-19-06 at 05:57. Reason: query edited...

Posting Permissions

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