SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Oct 2002
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool Mysql- Join Problem

    AS I know, MYSQL don't support full outer join
    so i have some problem for my query,
    I have 2 table
    table 1
    { id
    date
    other infomation....
    }

    table 2
    { id
    date
    remark
    }
    now i need to show a table something like other join,
    show the record once it exist on each table or both,
    how can i do this?

    THANKS~

  2. #2
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)


    select * from table1, table2
    voila! full/cross join!

    But I'm not sure that you want to do a full join
    If you want to search for some value in table1 and table2 and display all matching rows from both tables, then I think it's a UNION you should use

  3. #3
    SitePoint Member
    Join Date
    Oct 2002
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for your reply~
    But using CROSS JOIN can't get what I want,

    let say table 1 have record
    staff_id date other field
    1 2002-08-10 ....
    1 2002-08-13 ....

    table 2 have record
    staff_id date remark
    1 2002-08-10 remark1
    1 2002-08-12 remark2

    i would like to have result like
    staff_id date remark other field
    1 2002-08-10 remark1 .....
    1 2002-08-12 remark2 x
    1 2002-08-13 x .....

    that is the result I would like to see~~

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    for a full outer join in mysql, you have to run at least two queries:
    Code:
    select a.col1, b.col2
      from a left outer join b
        on a.id = b.id
    
    select a.col1, b.col2
      from a right outer join b
        on a.id = b.id
     where a.id is null
    the first query gets

    1. all the matched rows
    2. all the a's that don't have a b

    while the second gets

    3. all the b's that don't have an a

    run these two queries and merge the results yourself

    or, create a temp table, run the first query into it, run the second into it, then select from the temp table

    rudy
    http://rudy.ca/


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
  •