Home How can I target an alias column name in the where clause when a same column name exists?

Questions

How can I target an alias column name in the where clause when a same column name exists?

March 24, 2023

So in the original table SELECT id FROM drivers there is a column teamid. However in my complicated query this teamid is not used for the output.

I am using a teamid but from another table as an alias.

SELECT
    id,
    (
        SELECT
            teamid
        FROM
            attendancelist
        WHERE
            activityid = 3
            AND driverid = driver.id
    ) as teamid
FROM
    drivers

When I try to use a WHERE-clause on teamid. It uses the teamid from driver and not the alias teamid. The only way I found to avoid this, is by renaming the alias to a column name not on the driver table, such as: customteamid.

But I was wondering, just like you can explicitly get teamid from driver with driver.teamid. Is there a way you can explicitly get the alias column name?

>Solution :

You can’t reference sub-query from SELECT clause in WHERE.

To do what you described you should use JOIN instead.
Something like this:

SELECT
    id,
    attendancelist.teamid
FROM
    drivers
    left join attendancelist on (
        attendancelist.activityid = 3
        AND attendancelist.driverid = drivers.id
    )
where
    attendancelist.teamid = 100500 -- YOU CONDITION HERE