Adding an auto_increment serial_id as a new col to a select statement

Question

Here is my query:

select * from a
JOIN B
ON
a.id=B.id
JOIN C
ON
B.id=C.id

How do I add one more col in the beginning called 'serial_id' which is auto incremental?

Ive seen examples here :MySQL query to select results with auto increment as a new column added in the result

But the solution doesn't work when i have joins in my select statement.

Answer 1

Try this way

SELECT *
,@a:= @a +1 `serial_no`
 FROM a
JOIN B
ON
a.id=B.id
JOIN C
ON
B.id=C.id
JOIN (SELECT @a:=0) t

or

SELECT *
,@a:= @a +1 `serial_no`
 FROM a , (SELECT @a:=0) t
JOIN B
ON
a.id=B.id
JOIN C
ON
B.id=C.id