MySQL Joining 3 tables

Question

I found this same question, followed the steps but still an error.
I've been searching this for many days.

I have 3 tables

user

+----+-----------------------------+-------------+ | id | fname | lname | +----+-----------------------------+-------------+ | 4 | John | Menezes | +----+-----------------------------+-------------+

group

+----+-----------------------------+-------------+ | group_id | group_name | created_on | +----+-----------------------------+-------------+ | 1 | Facebook friends | 25/12/2012 | +----------+-----------------------+-------------+

group_members

+----+-------------+-------------+ | id | user_id | group_id | +----+-------------+-------------+ | 4 | 4 | 1 | +----+-------------+-------------+

I want to get Somewhat like this Final Output

+----+--------------+-----------------------+ | fname | lname | groupname | +----+--------------+-----------------------+ | John | Menezes | Facebook Friends | +----+--------------+-----------------------+

I used this query

SELECT u.fname, u.lname, g.group_name FROM user AS u LEFT JOIN group_members AS gm ON gm.user_id = u.id INNER JOIN group AS g ON g.group_id = gm.group_id

But its giving me this error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group AS g ON g.group_id = gm.group_id LIMIT 0, 30' at line 4

Also tried this

where g.low_limit > 70 and g.high_limt < 120

dev.mysql.com/doc/refman/5.0/en/reserved-words.html

cheesemacfly
– cheesemacfly

2013-04-01 20:23:03 +00:00
Commented Apr 1, 2013 at 20:23 — cheesemacfly
– cheesemacfly, Commented Apr 1, 2013 at 20:23

Scott · Accepted Answer · 2013-04-01 20:24:29Z

3

The problem you are running into is that group is a reserved word in mysql http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html. You need to put the table and column names in back ticks ~~single quotes~~ for MySQL to interpret the name as a table.

try

SELECT `u`.`fname`, `u`.`lname`, `g`.`group_name` FROM `user` AS `u` LEFT JOIN `group_members` AS `gm` ON `gm`.`user_id` = `u`.`id` INNER JOIN `group` AS `g` ON `g`.`group_id` = `gm`.`group_id`

Edited: Name of character change. Thanks to @adrian

answered Apr 1, 2013 at 20:24

Scott

12.4k4 gold badges30 silver badges48 bronze badges

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

6 Comments

Adrian Over a year ago

That's backticks, not single quotes - correct in your code, just not in your explanation.

praveen.menezes Over a year ago

ohhh God this was so simple

Scott Over a year ago

Welcome to the life of a programmer.

ChoiZ Over a year ago

I have lost so many times with a field name "like" and "unlike" (like is also reserved…) so my advise now. ALWAYS use `` in you query ;)

praveen.menezes Over a year ago

Actually I have to make a website for my college project, developing it in PHP... I ws trying to find out the errors in this query.. ws Totally confused

|

apomene · Accepted Answer · 2013-04-01 20:23:42Z

try:

SELECT u.fname, u.lname, g.group_name FROM user AS u LEFT JOIN group_members AS gm ON gm.user_id = u.id INNER JOIN group AS g ON g.group_id = gm.id

Collectives™ on Stack Overflow

MySQL Joining 3 tables

2 Answers 2

6 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Related