MYSQL Multiple Table

Question

I want to use multiple table to get recent post (Q) and user name (handle) but when I am trying to get with this code it is giving error

Table Name qa_posts and qa_users and need to connect userid from qa_posts to handle from qa_users

SELECT *
FROM qa_posts, qa_users
WHERE type="Q" AND qa_posts.userid = "qa_users.handle"
ORDER BY created DESC
LIMIT 5

Error message

SELECT *
FROM qa_posts, qa_users
WHERE qa_posts.userid = "qa_users.handle"
ORDER BY created DESC
LIMIT 5

Error 1052: Column 'created' in order clause is ambiguous

Actual Code

qa_db_connection();
$query_post = qa_db_query_raw('         
    SELECT *
    FROM qa_posts, qa_users
    WHERE type="Q" AND qa_posts.userid = "qa_users.handle"
    ORDER BY created DESC
    LIMIT 5

') or die(mysql_error());

while($row = mysql_fetch_array($query_post)) {
    echo $row['title'];
    echo '<span style="font-size:65%;font-style:italic">'.$row['created'].'</span>';
    echo '<br/>';
}

See this image for table structure

Answer 1

The error is telling you have you have multiple columns with the name created so you need to specify which table the created field is coming from to perform the ORDER BY:

SELECT *
FROM qa_posts, qa_users
WHERE qa_posts.userid = "qa_users.handle"
ORDER BY qa_posts.created DESC
LIMIT 5

or:

SELECT *
FROM qa_posts, qa_users
WHERE qa_posts.userid = "qa_users.handle"
ORDER BY qa_users.created DESC
LIMIT 5

Edit #1, without seeing the full table structure it looks like you might want to do this you had the qa_users.handle in quotes, these should probably be removed:

SELECT *
FROM qa_posts
INNER JOIN qa_users
   ON qa_posts.userid = qa_users.handle
ORDER BY qa_posts.created DESC
LIMIT 5

Edit #2 you are joining on the wrong field:

SELECT *
FROM qa_posts
INNER JOIN qa_users
   ON qa_posts.userid = qa_users.userid
WHERE qa_posts.type="Q" 
ORDER BY qa_posts.created DESC
LIMIT 5

Answer 2

The given error cames out when you do have same name column in both table...So you have to specify column name along with table name like in following patter:

table_name.column_name

So, your query should be like this:

SELECT *
FROM qa_posts, qa_users
WHERE qa_posts.userid = "qa_users.handle"
ORDER BY qa_posts.created DESC (or put the ORDER BY qa_users.created DESC if you want to make order by `created` of `qa_users` )
LIMIT 5

If you really not desired to have userid of value "qa_users.handle", you should not quote them and should have query like this:

    SELECT *
    FROM qa_posts, qa_users
    WHERE qa_posts.userid = qa_users.userid AND qa_posts.type="Q" 
    ORDER BY qa_posts.created DESC 
    LIMIT 5

You would better to use JOIN in following way:

    SELECT *
    FROM qa_posts INNER JOIN qa_users ON qa_posts.userid = qa_users.userid 
    WHERE qa_posts.type="Q" 
    ORDER BY qa_posts.created DESC 
    LIMIT 5