MySQL LEFT JOIN isn't correct

Question

Please help someone. There was a question select from few tables mysql. A get an answer, read the documentation about LEFT JOIN http://www.mysql.ru/docs/man/LEFT_JOIN_optimisation.html (russian language). So I have a working query:

select con.connection_id, com.company_name, ctx.contact_name 
from connections as con 
left join companies as com
on con.company_id = com.company_id
left join contacts as ctx
on con.contact_id = ctx.contact_id
WHERE com.name=LIKE ('%qwer%')

Thats work good. I have new table called addresses with two fields(id and street). In table companies there is a field with address_id. Make a new query:

select con.connection_id, com.company_name, ctx.contact_name 
from addresses as add, connections as con 
left join companies as com
on con.company_id = com.company_id
left join contacts as ctx
on con.contact_id = ctx.contact_id
where add.id=com.Legal_address
AND com.name=LIKE ('%qwer%')

It says:

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 'add, Connections as con LEFT JOIN Companies as com on con.company_id = com.id LE' at line 1

What I did wrong?

You should use all `join`s and `on`s, dont mix the comma/where joining syntax. — chris85, Jun 19 '17 at 12:02
Possible duplicate of [Syntax error due to using a reserved word as a table or column name in MySQL](https://stackoverflow.com/questions/23446377/syntax-error-due-to-using-a-reserved-word-as-a-table-or-column-name-in-mysql) — chris85, Jun 19 '17 at 12:08

ScaisEdge · Accepted Answer · 2017-06-21T16:40:19.127

1

you are mixing explicit and implicit join ..so you have table separated with comma .. remove it and use explicit join

select 
    con.connection_id
    , com.company_name
    , ctx.contact_name 
from addresses as `add`
inner join connections as con on `add`.id=con.Legal_address
left join companies as com on con.company_id = com.company_id
left join contacts as ctx on con.contact_id = ctx.contact_id

edited Jun 21 '17 at 16:40

answered Jun 19 '17 at 12:02

ScaisEdge

131,976
10
91
107

'from addresses as add inner join connections as con on add.id=com.Legal_address' ??? – symcbean Jun 19 '17 at 12:12
@scaisEdge Strange, but it says "Unknown column 'com.Legal_address' in 'on clause' " – kliukovking Jun 19 '17 at 12:44
@RoGGeR there was an error ... com instead of con answer updated – ScaisEdge Jun 19 '17 at 14:34
Is this really working while using a reserved word as an alias? – piet.t Jun 21 '17 at 08:57
@piet.t .. yes ,, i have seen your comment above ... many thanks ..now work anyway ... – ScaisEdge Jun 22 '17 at 10:06

symcbean · Answer 2 · 2017-06-19T12:14:20.337

1

Leaving aside the question of why it makes any sense to apply left joins from a table which appears to decompose a N:M relationship, mixing traditional and explicit join syntax is very messy.

A further WTF in your code is that there no obvious reason for adding the addresses table - since you don't actually use the attributes from it.

The reason your code is failing is that 'ADD' is a reserved word in MySQL. You could quote it, but (again ignoring the elephants in the room) the query would be better rewritten using a consistent syntax:

select con.connection_id, com.company_name, ctx.contact_name 
from addresses as add, connections as con 
left join (SELECT company_name, company_id
   FROM companies AS c
   INNER JOIN addresses AS a
     ON c.legal_address=a.id) as com 
 on con.company_id = com.company_id
left join contacts as ctx on con.contact_id = ctx.contact_id
;

.....but the confused context here makes it impossible to say if this will produce the results you desire.

edited Jun 19 '17 at 12:14

answered Jun 19 '17 at 12:08

symcbean

47,736
6
59
94

Why do the `companies` need to be a sub-queried (will `com.company_id` be accessible like that?) – chris85 Jun 19 '17 at 12:11
@symcbean Thanks for your answer. Just now translate it(because of my bad english). But if I need to write at the end of the query "WHERE com.name like(".$query.") - how it look using joins? – kliukovking Jun 19 '17 at 12:21
@RoGGeR `WHERE com.name like ?` then prepare the statement and bind `$query` with whatever wildcards you want appended/prepended. – chris85 Jun 19 '17 at 12:22
@chris85 Yes, `RLIKE` to be precise – kliukovking Jun 19 '17 at 12:37
@Rogger: Ideeally the filter predicate would be in the sub-query `INNER JOIN address a ON c.legal_address=a.id WHERE c.name LIKE '$query' ) AS com`. Although it could simply be appended to the end of the query above - however the use of such a predicate PROVES you should not be using LEFT JOINs in what you describe as a "working query" – symcbean Jun 19 '17 at 12:50
1

@chris85: it doesn't *need* to be - but it helps me understand the logic/behaviour of the query when inner joins are embedded in outer joins. Also, depending on the overall structure of the query and the underlying data it *usually* results in a better plan from the optimizer. – symcbean Jun 19 '17 at 13:35

score 0 · Answer 3 · answered Jun 19 '17 at 12:05

It seems like you want to use another join here instead. Try something like this:

select con.connection_id, com.company_name, ctx.contact_name
from addresses as add
left join companies as com on add.id = com.Legal_address
left join connections as con on con.company_id = com.company_id
left join contacts as ctx on con.contact_id = ctx.contact_id

MySQL LEFT JOIN isn't correct

3 Answers3