Cosmin Cosmin - 2 months ago 10
SQL Question

MySQL join two tables by a column which needs to be incremented by a number

I need to add the number 100000 to all the clients ids in the

clients
table when I'm performing a join. Seems that
(100000 + c.id) AS c.id
works fine in the
SELECT
, but I get an error on the `LEFT JOIN clients c ON p.client_id = (100000 + c.id)' join.

The query looks like this:

SELECT p.id, p.date, p.document, (100000 + c.id) AS c.id, c.name
FROM payments p
LEFT JOIN clients c ON p.client_id = (100000 + c.id)
WHERE p.date < CURRENT_DATE


Is there any way I can achieve this?

Answer

try remove the tablename from the alias (id and not c.id)

SELECT p.id, p.date, p.document, (100000 + c.id) AS  id, c.name
FROM payments p
LEFT JOIN clients c ON p.client_id = 100000 + c.id
WHERE p.date < CURRENT_DATE
Comments