SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2004
    Location
    Glen Ellyn
    Posts
    55
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Join two mySQL tables

    I have two tables, Table1 has the following fields: ID (Unique), City, State, Zip and Comments. Table2 has: ID (Non-Unique - corresponds to the same ID in the first table), comments and name.

    The first table gives a location and original comments of the poster, the second table holds a bunch comments to the original post. I need to write a SQL statement that combines these two tables and where the ID's are equal. So my query would search by City and State. The output would have ID, City, State, Zip, Table1.comments, Table2.comments[0], Table2.comments[1], etc.... So the query would append to the end all comments in Table2.

    Does this make sense?

    Thanks,
    Adam

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    yeah, it makes sense, but not the array of comments

    you can't get that (easily) from sql, you have to do that in your script

    what you describe is a join, and in the case of comments, there's an unfortunate side effect

    from the first table, you will get results like this --

    ID City State Zip Comments
    13 TORONTO ON M3J4R7 Hey wasn't that a wicked game between Ottawa and Winnipeg last night
    14 EUREKA KS 90210 Did you see the Survivors All Stars Finale?

    now, if you join this table to the second table, every table 2 row with a matching ID will be joined to each table 1 row, like this --

    ID City State Zip Comments ID2 Name Comments2
    13 TORONTO ON M3J4R7 Hey wasn't that a wicked game between Ottawa and Winnipeg last night? 52 Billy Yeah, it was awesome
    13 TORONTO ON M3J4R7 Hey wasn't that a wicked game between Ottawa and Winnipeg last night? 53 Tommy Did you see the Winnipeg punt just before halftime?
    13 TORONTO ON M3J4R7 Hey wasn't that a wicked game between Ottawa and Winnipeg last night? 54 Billy Oh man, a 92 yard punt, yeah that was fantastic
    13 TORONTO ON M3J4R7 Hey wasn't that a wicked game between Ottawa and Winnipeg last night? 55 Tommy Plus, the punter just got a first down on the series before
    13 TORONTO ON M3J4R7 Hey wasn't that a wicked game between Ottawa and Winnipeg last night? 56 Freddy Tremendous game
    13 TORONTO ON M3J4R7 Hey wasn't that a wicked game between Ottawa and Winnipeg last night? 57 Marvin What game was that?
    14 EUREKA KS 90210 Did you see the Survivors All Stars Finale? 73 Mary Oh that Boston Rob is such a hunk
    14 EUREKA KS 90210 Did you see the Survivors All Stars Finale? 74 Betty I thought the reunion in the studio was so lame
    14 EUREKA KS 90210 Did you see the Survivors All Stars Finale? 75 Wilma But that Jeff PRobst is so C-U-T-E
    14 EUREKA KS 90210 Did you see the Survivors All Stars Finale? 76 Betty What planet are you from?


    this is one example where i would run two queries instead of a join
    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
  •