Results 1 to 3 of 3

Thread: query problem

  1. #1
    Join Date
    Sep 2002
    Posts
    8

    Unanswered: query problem

    I'm having some problem creating a SELECT query (hopefully, with one or two SQL statement only) that would output the contents of two tables. Example, given the table1 and table2 as follows:

    table1
    ------------------------
    ID | data1 | data2
    ------------------------
    1 | 100 | aaa
    2 | 200 | bbb
    3 | 300 | ccc
    4 | 400 | ddd
    5 | 500 | eee
    ------------------------
    table2
    ------------------------
    ID | info1 | info2
    ------------------------
    1 | 11 | xyz
    2 | 22 | abc
    3 | 33 | 123
    7 | 77 | 456
    8 | 88 | opq
    ------------------------
    the output of the query from table1 and table2 should be as follows:
    ---------------------------------------------------
    ID | data1 | data2 | info1 | info2
    ---------------------------------------------------
    1 | 100 | aaa | 11 | xyz
    2 | 200 | bbb | 22 | abc
    3 | 300 | ccc | 33 | 123
    4 | 400 | ddd | |
    5 | 500 | eee | |
    7 | | | 77 | 456
    8 | | | 88 | opq
    ---------------------------------------------------

    as you could see, there are some records present in both tables and some don't, but I need all the data to be returned by the SELECT query. How will I do this?

    Thanks in advance.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what you seek is a full outer join

    if your mysql is version 4.0 or greater, you can accomplish this in one query by using a union of a left outer join with a right outer join

    failing that, you will have to run the left outer join and right outer join separately, and combine the results yourself

    select table1.ID, data1, data2, info1, info2
    from table1
    left outer join table2
    on table1.ID = table2.ID

    this will give you everything except the rows of table2 that don't have a match in table1, so you have to run

    select table1.ID, data1, data2, info1, info2
    from table1
    right outer join table2
    on table1.ID = table2.ID
    where table1.ID is null

    note that without the check for null in this second query, you would get matching rows again, which you already got in the first query

    rudy
    http://rudy.ca/

  3. #3
    Join Date
    Sep 2002
    Posts
    8
    I'm using version 3.23. Follow up question, what if I had two primary keys in the table1 and table2 aside from ID, say P_ID. Example:

    table1
    -----------------------------
    P_ID | ID | data1 | data2
    -----------------------------
    P1 | 1 | 100 | aaa
    P1 | 2 | 200 | bbb
    P1 | 3 | 300 | ccc
    P1 | 4 | 400 | ddd
    P1 | 5 | 500 | eee
    P2 | 1 | 100 | bbb
    -----------------------------
    table2
    -----------------------------
    P_ID | ID | info1 | info2
    -----------------------------
    P1 | 1 | 11 | xyz
    P1 | 2 | 22 | abc
    P1 | 3 | 33 | 123
    P1 | 7 | 77 | 456
    P1 | 8 | 88 | opq
    P2 | 1 | 11 | xyy
    P2 | 5 | 55 | xyy
    ------------------------------

    but this time, I'm getting all the records which has a P_ID equal to P1.
    First, I executed the query:

    SELECT table1.ID, table1.data1, table1.data2, table2.info1, table2.info2 FROM table1 LEFT OUTER JOIN table2 ON table1.ID AND table2.ID WHERE table1.P_ID="P1" ;

    -> i was able to get all the records that exist on both tables and also the records that only exist in table1. Afterwards, I executed another query in order to get the records that only exist in table2. so, I run the following SQL:

    SELECT table2.ID, table1.data1, table1.data2, table2.info1, table2.info2 FROM table1 RIGHT OUTER JOIN table2 ON table1.ID=table2.ID where table1.ID IS NULL AND table2.P_ID="P1" ;

    -> but there's no result. I also tried rearranging the statement to something like:

    SELECT table2.ID, table1.data1, table1.data2, table2.info1, table2.info2 FROM table2 LEFT OUTER JOIN table1 ON table1.ID=table2.ID where table1.ID IS NULL AND table2.P_ID="P1" ;

    -> I got wrong result... it seems that the problem is in the "where" clause.. any suggestions?

    Thanks in advance.

Posting Permissions

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