mySQL join question:

Databases
1

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

 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?

2

What fields (and field types) do you have in each table?

3

The tables are the same

email table

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

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
4

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)

5

Thanks. That’s what the HTML layout calls for. I did it in php and it was quite easy. :wink: