How do I select the id, user_id specifically from the comments table and how do I select username, date, posts from the users table specifically?
here is the code.
SELECT users.*, comments.*
+2
A:
SELECT c.id, c.user_id, u.username, u.date, u.posts
FROM comments c, users u
WHERE [something]
Dan D.
2010-07-09 04:18:03
what is the c and u?
avoid
2010-07-09 04:19:08
Note the c and u after comments and users; those declare "aliases" for the tables, which makes referring to them easier.
Michael Louis Thaler
2010-07-09 04:20:14
It's called an alias. It allows you to shorten the name of the table when used in your query, or to have two calls to the same table that have different aliases.
Dan D.
2010-07-09 04:21:19
Insert a witty comment about feeding a man or teaching him to fish here. :(
Unkwntech
2010-07-09 06:58:29
A:
"SELECT id, user_id FROM comments";
"SELECT username, date, posts FROM users";
klox
2010-07-09 04:19:28
A:
Don't forget to use join, Dan's answer would result to cartesian product (i.e. table comma table) if you inadvertently forgot to put the joining condition in WHERE clause
SELECT c.id, c.user_id, u.username, u.date, u.posts
FROM comments c join users u using(user_id)
WHERE [something]
Hao
2010-07-09 04:40:45
A:
Based on your question, I suppose you don't know what to put in the WHERE [something] in the other answers.
SELECT c.id, c.user_id, u.username, u.date, u.posts
FROM comments c, users u
WHERE c.user_id = u.id
Assuming the user id field in users table is called id.
You could use also:
SELECT c.id, c.user_id, u.username, u.date, u.posts
FROM users u
INNER JOIN comments c ON c.user_id = u.id
Anyways, this will solve your problem but I would recommend some MySQL (or SQL in general) readings like MySql Manual
laurent-rpnet
2010-07-09 14:40:14