CODEWITHSUNDEEP
mysql
ccc
ccc

Reputation: 161

MySQL Select and Join - Ambiguous column

I'm trying to join four tables together. The code works fine when I'm only comparing the EVENT_DATA and joining the PERSONA tables as I'm able to get the "Name" column from PERSONA (which doesn't exist in the EVENT_DATA table). However, one of the problems is that this "Name" column also exists in the CUSTOMCAR table, so I can only get one or the other. Additionally, when I tried adding the last join statement, the code wouldn't run at all.

If someone could please help me, that would be great!

$sql = "SELECT * FROM EVENT_DATA
LEFT JOIN PERSONA ON EVENT_DATA.personaId = PERSONA.ID
LEFT JOIN CUSTOMCAR ON CUSTOMCAR.ownedCarId = EVENT_DATA.carId
LEFT JOIN CARCLASSES ON CARCLASSES.store_name = CUSTOMCAR.name
WHERE (EVENT_DATA.EVENTID = '299')";

Upvotes: 2

Views: 629

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175596

You should avoid * and use explicit columns list instead:

SELECT EVENT_DATA.personaId, ...
FROM EVENT_DATA
LEFT JOIN PERSONA ON EVENT_DATA.personaId = PERSONA.ID
LEFT JOIN CUSTOMCAR ON CUSTOMCAR.ownedCarId = EVENT_DATA.carId
LEFT JOIN CARCLASSES ON CARCLASSES.store_name = CUSTOMCAR.name
WHERE (EVENT_DATA.EVENTID = '299');

If you have same column name you need to to use aliasing:

SELECT ...
 CUSTOMCAR.NAME AS c_name,
 PERSONA.NAME AS p_name
...

Upvotes: 1

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

You could also use Aliasing:

$sql = "SELECT ed.*, 
               pa.*, 
               cc.*, 
               ccs.* 
        FROM EVENT_DATA AS ed
        LEFT JOIN PERSONA AS pa ON ed.personaId = pa.ID
        LEFT JOIN CUSTOMCAR AS cc ON cc.ownedCarId = e.carId
        LEFT JOIN CARCLASSES AS ccs ON ccs.store_name = cc.name
        WHERE (ed.EVENTID = '299')";

Note: Although as suggested by @Lukasz, you should really avoid using wildcard (*), and provide an explicit list of columns in the SELECT clause.

Upvotes: 1

Related Questions