Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2007
    Posts
    25

    Unanswered: Joining two tables

    [FONT="Arial"] I am trying to join two tables as part of a CREATE TABLE AS
    statement. I have tried using all types of JOIN (left, right, cross...) my problem is that I need the output to show entries from both side whether there is a match or not


    This are the source tables:
    l7 l8
    group_id loy7 group_id loy8
    1 7 1 5
    2 2 3 1
    4 3 4 7

    This is the result I got:

    Group_id loy7 loy8
    1 7 5
    2 2
    4 3 7



    This is what I need to get:

    Group_id loy7 loy8
    1 7 5
    2 2
    3 1
    4 3 7


    Any suggestions?
    Last edited by obwan; 12-26-08 at 14:43.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My guess would be a cross join on group_id.

    -PatP

  3. #3
    Join Date
    Mar 2007
    Posts
    25
    Thank you Pat, I ran the following

    create table obr_loytran_78z as select l7.group_id, l7.loyalty_trans loy7,
    l8.loyalty_trans loy8
    from obr_loyalty2007 l7
    cross join obr_loyalty2008 l8 on(l8.group_id)
    order by l7.group_id;

    and received a 263,371 rows results, the original l7 table has 730 rows and the l8 table has 915 rows.

    I'm puzzled.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Your ON clause is sub-optimal... You need a more complex expression like I7.order_id = I8.order_id.

    -PatP

  5. #5
    Join Date
    Mar 2007
    Posts
    25
    I will try that, thank you

  6. #6
    Join Date
    Mar 2007
    Posts
    25
    I ran the following and it did not work it returned only rows with matching group_id

    create table obr_loytran_78z as select l7.group_id, l7.loyalty_trans loy7,
    l8.loyalty_trans loy8
    from obr_loyalty2007 l7
    cross join obr_loyalty2008 l8 on(l7.group_id = l8.group_id)
    order by l7.group_id;

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My bad... According to the documentation MySQL currently handles CROSS joins as though they were INNER joins, where at least with Inno-Db MySQL used to handle CROSS joins as FULL joins. There may be a new exception to JOIN handling that allows MySQL to do what SQL calls a FULL join, but I'd recommend doing the "heavy lifting" yourself.

    Keep in mind that this is pure "air code", it has not been tested at all:
    Code:
    CREATE TABLE obr_loytran_78z
    AS SELECT l71.group_id, l71.loyalty_trans loy7, l81.loyalty_trans loy8
       FROM obr_loyalty2007 l71
       LEFT JOIN obr_loyalty2008 l81
          ON (l71.group_id = l81.group_id)
       UNION SELECT l72.group_id, l72.loyalty_trans loy7, l82.loyalty_trans loy8
       FROM obr_loyalty2007 l72
       RIGHT JOIN obr_loyalty2008 l82
          ON (l72.group_id = l82.group_id)
       WHERE  I72.group_id IS NULL
       ORDER BY l71.group_id;
    -PatP

  8. #8
    Join Date
    Mar 2007
    Posts
    25
    Sorry Pat, still did not work, I'm giving up. HAPPY NEW YEAR

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT Coalesce(l7.group_id, l8.group_id) As [group_id]
         , l7.loy7
         , l8.loy8
    FROM   l7
     FULL
      JOIN l8
        ON l7.group_id = l8.group_id
    ?
    George
    Home | Blog

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Oh and of course you can just use UNION and remove the excusion join
    Code:
    SELECT l7.group_id
         , l7.loy7
         , l8.loy8
    FROM   @l7 l7
     LEFT
      JOIN @l8 l8
        ON l7.group_id = l8.group_id
    
    UNION
    
    SELECT l8.group_id
         , l7.loy7
         , l8.loy8
    FROM   @l8 l8
     LEFT
      JOIN @l7 l7
        ON l7.group_id = l8.group_id
    *shrug*
    George
    Home | Blog

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT l7.group_id
         , l7.loy7
         , l8.loy8
    FROM   @l7 l7
     LEFT
      JOIN @l8 l8
        ON l7.group_id = l8.group_id
    
    UNION ALL
    
    SELECT l8.group_id
         , l7.loy7
         , l8.loy8
    FROM   @l8 l8
     LEFT
      JOIN @l7 l7
        ON l7.group_id = l8.group_id
    WHERE  l7.group_id IS NULL
    Because MySQL is funny with FULLs, in't it!
    George
    Home | Blog

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Those last two posts have appeared in the wrong order... oh well!
    George
    Home | Blog

Posting Permissions

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