join tables?

[Mark]

hi. i have two tables.

table one ("users") contains two columns, "user_id", and "user_name"
table two ("spam") contains three columns "msg_id","user_id", and "msg"

basically, i want to retrieve all the "spam" entries, but instead of
displaying the user_id, i want it to show the user_name, from table
one. (using php)

i'm assuming i need to use this JOIN thing, but i've never used it
before. any help?

[Mark]

Mark wrote:

hi. i have two tables.

table one ("users") contains two columns, "user_id", and "user_name"
table two ("spam") contains three columns "msg_id","user_id", and "msg"

basically, i want to retrieve all the "spam" entries, but instead of
displaying the user_id, i want it to show the user_name, from table
one. (using php)

i'm assuming i need to use this JOIN thing, but i've never used it
before. any help?

err..by sort of guessing, i came up with "SELECT * FROM spam LEFT JOIN
users USING (user_id)" which seems to work alright. is that how you're
supposed to do it?

[davie]

Mark wrote:

hi. i have two tables.

table one ("users") contains two columns, "user_id", and "user_name"
table two ("spam") contains three columns "msg_id","user_id", and "msg"

basically, i want to retrieve all the "spam" entries, but instead of
displaying the user_id, i want it to show the user_name, from table
one. (using php)

i'm assuming i need to use this JOIN thing, but i've never used it
before. any help?

SELECT users.user_name,spam.msg_id ,spam.msg FROM users.spam WHERE
spam.user_id = users..user_id

[Bill Karwin]

Mark wrote:

err..by sort of guessing, i came up with "SELECT * FROM spam LEFT JOIN
users USING (user_id)" which seems to work alright.

It might work a bit faster as following:

SELECT * FROM spam JOIN users USING (user_id);

You would use LEFT JOIN if you thought that there might be no entries in
the users table matching the user_id given in the spam table. I would
expect in your case that there should be a user in all such cases, so a
simple inner join would be fine. Inner joins often run quicker than
outer joins.

Regards,
Bill K.

[Mark]

Bill Karwin wrote:

Mark wrote:

err..by sort of guessing, i came up with "SELECT * FROM spam LEFT JOIN
users USING (user_id)" which seems to work alright.

It might work a bit faster as following:

SELECT * FROM spam JOIN users USING (user_id);

You would use LEFT JOIN if you thought that there might be no entries in
the users table matching the user_id given in the spam table. I would
expect in your case that there should be a user in all such cases, so a
simple inner join would be fine. Inner joins often run quicker than
outer joins.

Regards,
Bill K.

ohhh.. i see. thank you! this helps.