SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist captainccs's Avatar
    Join Date
    Mar 2004
    Location
    Caracas, Venezuela
    Posts
    516
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    mySQL join question:

    I have two tables I want to join. One has phone numbers and the other email addresses. Both need to be selected by 'crew_id'. Let's say crew_id 1 has three phone numbers and two email addresses, I want the resulting table to look like this
    Code:
     place | number   | eplace | email
     home  | 555-1334 | xyz    | me@home.com
     work  | 444-6543 | abc    | me@yahoo.com
     other | 777-5454 |        |
    Can this be done with MySQL version 5.0? If so, how?

    Or is it better to build the table from two separate mySQL queries?
    Denny Schlesinger
    web services

  2. #2
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,032
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    What fields (and field types) do you have in each table?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  3. #3
    SitePoint Evangelist captainccs's Avatar
    Join Date
    Mar 2004
    Location
    Caracas, Venezuela
    Posts
    516
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    The tables are the same

    email table
    Code:
    id             mediumint primary
    boat_id        mediumint index
    crew_id        mediumint index
    eplace         varchar 30
    email          varchar 30
    date_created   not used
    ip_created     not used
    date_modified  not used
    ip_modified    not used
    phone table
    Code:
    id             mediumint primary
    boat_id        mediumint index
    crew_id        mediumint index
    place          varchar 30
    number         varchar 30
    date_created   not used
    ip_created     not used
    date_modified  not used
    ip_modified    not used
    Denny Schlesinger
    web services

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by captainccs View Post
    I want the resulting table to look like this
    that's not possible, without going to ~great~ lengths to assign an artificial "position 1" to the first number, "position 2" to the second, and so on, then "position 1" to the first email, "position 2" to the second, and so on, then joining based on the position numbers

    reason being that rows in database tables don't have inherent position numbers

    so if you want that exact resulting layout, do two queries and combine the results in your application language (php or whatever)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist captainccs's Avatar
    Join Date
    Mar 2004
    Location
    Caracas, Venezuela
    Posts
    516
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    that's not possible....

    so if you want that exact resulting layout, do two queries and combine the results in your application language (php or whatever)
    Thanks. That's what the HTML layout calls for. I did it in php and it was quite easy.
    Denny Schlesinger
    web services


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
  •