MYSQL join query syntax

Question

I am trying to join three tables. The first is a table of groups, the second of joiners and the third of users. Logic is I start out with id of group, then join group on joiners by groupid. Finally since I want names as well as ids I pull them from users table by joining users on userid. But alas not working. MYSQL is throwing an error saying "error near 'groups' g which usually means right before that. What am I missing?

groups id | name |userid

joiners id | groupid | userid

users id | firstname

$sql = "SELECT g.*,j.userid,u.firstname,u.id
FROM 'groups' g
LEFT JOIN 'joiners' j
ON g.id = j.groupid
LEFT JOIN 'users' u
ON j.userid = u.id
WHERE g.id = 22";

You say that it is reporting "error near 'group' g", however, in your SQL, it appears to be "'groups' g" -- did you recently change the name of the table "group" to "groups"? Since "group" is a reserved word, it would be a bad choice for a table name. — Jeremy Goodell
– Jeremy Goodell, Commented May 31, 2012 at 15:26
that was a typo. Since this is on test server, I tried changing field name and no difference. — user1260310
– user1260310, Commented May 31, 2012 at 16:19

Ja͢ck · Accepted Answer · 2012-05-31 15:24:47Z

2

Ambiguous terms such as tables and columns should be escaped using back ticks:

$sql = "SELECT g.*,j.userid,u.firstname,u.id
FROM `groups` g
LEFT JOIN `joiners` j ON g.id = j.groupid
LEFT JOIN `users` u ON j.userid = u.id
WHERE g.id = 22

This is assuming the rest of your query is correct, because I don't see the end of your string ;-)

answered May 31, 2012 at 15:24

Ja͢ck

174k39 gold badges269 silver badges317 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1260310 Over a year ago

the table names are all escaped with back ticks. Are you recommending I escape all the columns as in 'j.userid', 'u.userid'? I don't see what you have changed in your version. There is a " and semicolon at end of sql otherwise it would have thrown a different error. Still at a loss....

Sebas Over a year ago

no, just the the ' is not appropriate for table escaping, use backtick instead (same for columns)

user1260310 Over a year ago

Yes, that was the problem, what I thought were ticks were apostrophes, not backticks. I thought they were backticks. Sorry, did not understand your answer. As former asp person, how I hate PHP punctuation. grrr.

Collectives™ on Stack Overflow

MYSQL join query syntax

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related