syntax error when trying to excute select with left join-CodePudding

my db has 3 tables as follow: First table is channels: id | name | link | group_id

Second table is: group: id | group | gstatus

I am trying to do a query :

SELECT channels.id, channels.group_id, channels.name, channels.link, group.group FROM channels AS channels LEFT JOIN group AS group ON channels.group_id = group.id

even I tried without (AS) like this :

SELECT channels.id, channels.group_id, channels.name, channels.link, group.group FROM channels LEFT JOIN group ON channels.group_id = group.id

but all what I get is

SELECT channels.id, channels.group_id, channels.name, channels.link, group.group FROM channels AS channels LEFT JOIN group AS group ON channels.group_id = group.id

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'group AS group ON channels.group_id = group.id' at line 3 Time: 0s

so may can somebody tell me what's wrong in my query?

Thanks a lot for @Akina, the problem was I used a reserved word for my table group, I just changed the name and now all fine. regards

CodePudding user response：

SELECT  channels.id,
    channels.group_id,
    channels.name,
    channels.link,
    group.group
FROM    channels AS channels 
LEFT JOIN `group` AS `group` 
ON channels.group_id = group.id

Use this backtick ( ` )

CodePudding user response：

There is no point to write

JOIN group AS group or FROM channels AS channels

because the only purpose of AS is to define an alias. If you want to refer by original table name then just omit AS:

SELECT channels.id, channels.group_id, channels.name, channels.link, group.group FROM channels 
LEFT JOIN `group` ON channels.group_id = group.id

UPD also group must be quoted because it's a reserved word

CodePudding user response：

This was the solution from @Akina, @Michael

GROUP is reserved word. Either quote it with backticks or rename the table (preferred). – Akina

Yes, as @Akina says, GROUP is a part of GROUP BY, so your table name is unfortunate. – Michael

Thank you so much guys