Problem with SQL query [Inner join and multiple selects]
here is my query:
SELECT * FROM
(SELECT messages.*,
users.*
FROM `afo_messages` as messages
INNER JOIN `key_users` as users ON messages.user_id = users.id
WHE开发者_开发技巧RE messages.category_id=5
ORDER BY messages.id DESC LIMIT 30) ilv
ORDER BY messages.id ASC
Error is "#1060 - Duplicate column name 'id'".
Table afo_messages columns:
id | user_id | category_id | parent_id | message | create_date | update_date | status
Table key_users columns:
id | name | email | phone | pwd | active | role | invite_id | download_link | date
What I'm doing wrong?
Thanx!
Pretty sure that when you run the inner query, the result set has two columns called ID (on from messages, one from users); you're then running a query on that subquery, and the parser doesn't understand it.
Try:
SELECT * FROM
(SELECT messages.id as message_id,
messages.user_id,
....all other columns from messages
users.id as real_user_id,
users.name,
..... all other columns from users
FROM `afo_messages` as messages
INNER JOIN `key_users` as users ON messages.user_id = users.id
WHERE messages.category_id=5
ORDER BY messages.id DESC LIMIT 30) ilv
ORDER BY message_id ASC
You are selecting two columns called ID
without aliasing one. Do you really need ALL of the columns from both tables. This is generally bad practice. You would probably be better off specifying exactly which columns you want and aliasing one of the ID
s. For example:
SELECT * FROM
(SELECT messages.ID,
messages.message,
users.id as UserId, --aliasing this column to UserId
users.name,
users.email
FROM `afo_messages` as messages
INNER JOIN `key_users` as users ON messages.user_id = users.id
WHERE messages.category_id=5
ORDER BY messages.id DESC LIMIT 30) ilv
ORDER BY messages.id ASC
Your internal query says SELECT messages.*, users.*...
and both afo_messages
and key_users
have an id
field. Pick one, or alias them.
You have two id
columns, one in each table. You need to not use SELECT *
and alias at least one of them to give it a distinct name.
SELECT * FROM (SELECT messages.id AS messageid, ...
It doesn't match the error message, but shouldn't your final ORDER BY be by ilv.id?
In the context of your outer query, no table named "message" was ever referenced.
精彩评论