Unknown column in 'field list' on inner join

user_2900733 0
Viewed 107

Hi I am not so experience with SQL and try to make this query work

select ad.ad_id as the_ad_id
     , ad_country.country_id
     , v.vertical
  from ad 
  left 
  join ad_country 
    on ad.ad_id = ad_country.ad_id 
  join 
     ( select ad_id 
         from ad_vertical 
        where ad_vertical.vertical = 'biz op'
     ) as v 
    on ad.ad_id = v.ad_id 
 limit 10

And I get the error:

Unknown column 'v.vertical' in 'field list

But when I'm the same query with left join without filtering it is working.

select ad.ad_id as the_ad_id
     , ad_country.country_id
     , v.vertical
  from ad 
  left 
  join ad_country 
    on ad.ad_id = ad_country.ad_id 
  left 
  join ad_vertical as v 
    on ad.ad_id = v.ad_id 
 limit 10

Can you help me figure out what am I doing wrong? Thanks , Or

mysql
2 Answers

In the first query, v is an alias for nested query:

inner join 
  (select `ad_id` from `ad_vertical` where `ad_vertical`.`vertical` = 'biz op') 
as `v`

... which doesn't have column vertical. In your second query, however, v is an alias for the whole ad_vertical table.

user_12290230 
[...]
inner join (select `ad_id` from `ad_vertical` where `ad_vertical`.`vertical` = 'biz op') 
as `v`

You are only selecting the column ad_id from the ad_vertical table. Your alias v will therefore only have that column ad_id. This means that you cannot use v.vertical in your outer SELECT query.

If you want to return the vertical column as well you have you adjust the INNER JOIN query to return it as well:

[...]
inner join (select `ad_id`, `vertical` from `ad_vertical` where `ad_vertical`.`vertical` = 'biz op') 
as `v`
user_2869340
Related