SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    You want what? By when?? Milamber's Avatar
    Join Date
    Jan 2001
    Location
    California
    Posts
    342
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Simple join issue...

    Simple join issue:


    right now i have two tables:
    table1 { CA, NJ, WY }
    table2 { CA, NY, WY }

    and the result i want is an array of { CA, NY, NJ, WY } with the use of a single query.

    How the heck do you do that? I know it involves "JOIN"s of some sort, but right now I simply can't seem to wrap my head around that concept.

    Also, any one that can point me in the direction of some good literature on the subject would be greatly appreciated.
    -Jeff Minard | jrm.cc - Battlefield 2 Stats

  2. #2
    FreeBSD The Power to Serve silver trophy pippo's Avatar
    Join Date
    Jul 2001
    Location
    Italy
    Posts
    4,514
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,
    could you be more specific about the tables layout ( fields ) ?
    What kind of relation do you want to make between table1 and table2 ?



    pippo
    Mr Andrea
    Former Hosting Team Advisor
    Former Advisor of '03

  3. #3
    You want what? By when?? Milamber's Avatar
    Join Date
    Jan 2001
    Location
    California
    Posts
    342
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sure:


    Code:
    Table1
    +----------------+
    | id    |  state |
    +----------------+
    | 1     |  CA    |
    | 2     |  NY    |
    | 3     |  WY    |
    +----------------+
    
    Table2
    +----------------+
    | id    |  state |
    +----------------+
    | 1     |  CA    |
    | 2     |  NJ    |
    | 3     |  WY    |
    +----------------+
    What i want back is:

    Code:
    Table
    +----------------+
    | id    |  state |
    +----------------+
    | 1     |  CA    |
    | 2     |  NJ    |
    | 2     |  NY    |
    | 3     |  WY    |
    +----------------+
    That all clear?
    -Jeff Minard | jrm.cc - Battlefield 2 Stats

  4. #4
    FreeBSD The Power to Serve silver trophy pippo's Avatar
    Join Date
    Jul 2001
    Location
    Italy
    Posts
    4,514
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Damn,
    your question was a bit complicated...for me at least
    It broken my head but I found a solution:

    Code:
    DROP TABLE IF EXISTS pippo;
    CREATE TABLE pippo select * from table1;
    INSERT INTO pippo select * from table2;
    SELECT * FROM pippo GROUP BY state;
    I tested it and it worked using mysql_front.
    That'sn't as single query...

    Maybe it could work using a temporary table too,
    but I'm not sure about that.

    I think that moving this thread in the database/mysql forum you will have more nice solutions from sql guru there.

    I'm not a sql guru...

    Mine works.



    pippo


    p.s.
    I was assumming that you were using mysql...
    Last edited by pippo; Sep 25, 2002 at 00:20.
    Mr Andrea
    Former Hosting Team Advisor
    Former Advisor of '03

  5. #5
    We like music. weirdbeardmt's Avatar
    Join Date
    May 2001
    Location
    Channel Islands Girth: Footlong
    Posts
    5,882
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    off the top of my head...

    PHP Code:
    SELECT table1.* from table1 LEFT JOIN table2 ON table1.state=table2.state WHERE table1.state=table2.state 
    something like that...
    I swear to drunk I'm not God.
    Matt's debating is not a crime
    Hint: Don't buy a stupid dwarf Clicky

  6. #6
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Moved to a more appropriate forum. I could have put this in the MySQL forum, but I don't want to guess what db you are using

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    milamber, what you want is a UNION, not a JOIN...
    Code:
    select id, state from table1
    union
    select id, state from table2
    order by id, state
    notice UNION instead of UNION ALL

    UNION removes duplicate rows

    if you are using mysql, UNION is not supported until version 4
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •