CODEWITHSUNDEEP
phpmysql
nick
nick

Reputation: 3229

How to avoid colliding column names when using a LEFT JOIN

I have table A, table B, and table C. Using a value from table A, I want to select things from table B and table C.

So I wrote this:

'SELECT * FROM a LEFT JOIN b ON a.w = b.x LEFT JOIN c ON a.y = c.z';

The problem is I have colliding column names in the three tables, and the last table column names overwrite the second one.

How can I fix this, I tried using the AS keyword but couldn't make it work, such as:

'SELECT * FROM a LEFT JOIN b AS bb ON a.w = bb.x LEFT JOIN c AS cc ON a.y = cc.z';

but it doesn't work. Maybe LEFT JOIN is not the best option? What I need to get is this:

a_id: u
a_name: v
b_id: w
b_name: x
c_id: y
c_name: z

As you can see, the array/object has a prefix for each table (table name + _) so there is no collision.

I'm hope I'm being clear, let me know if I'm not so I can edit the post. Thanks.

Upvotes: 1

Views: 726

Answers (1)

Nick
Nick

Reputation: 147146

You need to provide aliases for each of the column names individually:

SELECT a.id AS a_id, a.name AS a_name,
       b.id AS b_id, b.name AS b_name,
       c.id AS c_id, c.name AS c_name
FROM a 
LEFT JOIN b ON a.w = b.x 
LEFT JOIN c ON a.y = c.z

Upvotes: 2

Related Questions