1

Now have the following problem. I am trying to get data from 3 tables using the following query.

SELECT Message, Name
FROM message, users
LEFT JOIN user_message ON ID = Message_id
LEFT JOIN user_message ON ID = User_id
WHERE user_message.status = 1
LIMIT 0 , 30

And here I am getting error 1066 what am I missing?

Saharsh Shah
  • 27,975
  • 8
  • 43
  • 82
user3673198
  • 59
  • 3
  • possible duplicate of [Why does this SQL code give error 1066 (Not unique table/alias: 'user')?](http://stackoverflow.com/questions/1435177/why-does-this-sql-code-give-error-1066-not-unique-table-alias-user) – idmean Jun 14 '14 at 07:51
  • You need to give aliases to the table (and fields), otherwise MySQL can't determine, for example, *which* `user_message.status` you're talking about. Moreover, the query has probably one JOIN too many. See Saharsh Shah's answer. – LSerni Jun 14 '14 at 07:56

2 Answers2

1

Try this: 

SELECT m.Message, u.Name
FROM message m
INNER JOIN user_message um ON m.ID = um.Message_id
INNER JOIN users u ON u.ID = um.User_id
WHERE um.status = 1
LIMIT 0, 30;
Saharsh Shah
  • 27,975
  • 8
  • 43
  • 82
0

You need to give alias and try like this

SELECT t1.Message, t2.Name
FROM message as t1
LEFT JOIN user_message as t2 ON t2.ID = t1.Message_id 
LEFT JOIN users as t3. ON t3.ID = t2.User_id
WHERE t3.status = 1
LIMIT 0 , 30
Deepu
  • 11,587
  • 13
  • 55
  • 88