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 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?
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
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.
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.