SQL query help: How can i get the column value from other table?

mysql> describe posts;
    +-------------+--------------+------+-----+---------+----------------+
    | Field       | Type         | Null | Key | Default | Extra          |
    +-------------+--------------+------+-----+---------+----------------+
    | id          | int(11)      | NO   | PRI | NULL    | auto_increment |
    | user_id     | int(11)      | NO   |     | NULL    |                |
    | title       | varchar(255) | NO   |     | NULL    |                |
    | body        | text         | YES  |     | NULL    |                |
    | created     | datetime     | YES  |     | NULL    |                |
    | modified    | datetime     | YES  |     | NULL    |                |
    | category_id | int(11)      | NO   |     | NULL    |                |
    | tags        | varchar(50)  | NO   |     | NULL    |                |
    +-------------+--------------+------+-----+---------+----------------+
    8 rows in set (0.00 sec)

    mysql> describe users;
    +----------+--------------+------+-----+---------+----------------+
    | Field    | Type         | Null | Key | Default | Extra          |
    +----------+--------------+------+-----+---------+----------------+
    | id       | int(11)      | NO   | PRI | NULL    | auto_increment |
    | username | varchar(255) | NO   | UNI | NULL    |                |
    | password | char(40)     | NO   |     | NULL    |                |
    | group_id | int(11)      | NO   |     | NULL    |                |
    | created  | datetime     | YES  |     | NULL    |                |
    | modified | datetime     | YES  |     | NULL    |                |
    +----------+--------------+------+-----+---------+----------------+
    6 rows in set (0.00 sec)

I'm trying to select column id,title from posts table and username from users table where posts.user_id = users.id.

If i select user_id from posts i will get only the integer value. But i want to get the equivalent varchar value from user开发者_StackOverflow中文版s table.

It would be a joining sql query. How can i do this?

Thanks in advance.

this should do the trick

SELECT p.id, p.title, u.UserName 
FROM posts p 
INNER JOIN users u ON p.user_id = u.id

or

SELECT p.id, p.title, u.UserName    
FROM posts p,users u
WHERE p.user_id = u.id

or

SELECT p.id, p.title, (select u.UserName FROM users u WHERE p.user_id = u.id)
FROM posts p 

In order of preference

You indeed need to use a join (most likely an inner join, here) between your two tables :

select posts.id, posts.title, users.username
from posts
    inner join users on users.id = posts.user_id

With this, for each post, you'll get the corresponding user's informations -- and you can add whatever field you want from users to the select part of the query.

Try this query:

 SELECT Posts.ID, Posts.Title, Users.UserName 
 FROM Posts INNER JOIN Users ON Posts.User_ID = Users.ID

Try this:

select posts.id, title, username
from posts join users
  on posts.user_id = users.id

If the column name is ambiguous (e.g., id), you can prefix it with the table name, as shown above.