MySQL: JOIN multiple tables

Question

I have the following query I am trying to join 2 tables (' Industry' , 'Country' ) on 2 conditions, but it gives me the following error

Error Code: 1054. Unknown column 'i.id' in 'on clause'

Does anybody know how should I tackle this?

SELECT c.name AS country_name, i.name as industry_name, num_projects, num_consultants, admin_rating
    FROM    industry i, country c 
    JOIN   (SELECT   pc.country_id, pi.industry_id, COUNT(p.id) AS num_projects
            FROM     project p, project_country pc, project_industry pi
            where p.id = pc.project_id and pi.project_id=p.id
            GROUP BY pc.country_id,pi.industry_id) x ON x.country_id = c.id  and x.industry_id=i.id
    JOIN   (SELECT   u.country_id,ie.industry_id, COUNT(u.id) AS num_consultants  
            FROM     user u, consultant_profile, industry_experience ie
            WHERE    u.is_active = 1 AND u.type = 0 and
                     ie.consultant_profile_id= consultant_profile.id 
                     and u.id= consultant_profile.id 
            GROUP BY u.country_id,ie.industry_id) y ON y.country_id = c.id and y.industry_id = i.id order by num_projects DESC limit 20;

EDIT the table structure is as following:

industry - id
project_industry - industry_id, project_id
industry_experience - consultant_profile_id, industry_id
consultant_profile - id,user_id

it is better to provide sql fiddle with all table `CREATE` statement. and you should avoid using `FROM t1, t2, t3` that should be `JOIN`, something like `FROM t1 JOIN t2 ON t1.t2_id=t2.id JOIN t3 ON t1.t3_id=t3.id` — Alex, Apr 28 '16 at 13:40
See if this solution fixes it : http://stackoverflow.com/questions/4065985/mysql-unknown-column-in-on-clause — Murlidhar Fichadia, Apr 28 '16 at 13:52
are you trying to group by *admin_rating* ? what table this column belongs to? — Alex, Apr 28 '16 at 14:29
are you sure about this condition? `where p.id = pc.project_id and pi.industry_id=p.id` so **p.id** equal to **pc.project_id** and at the same time to **pi.industry_id**? — Alex, Apr 28 '16 at 14:39
@ALex admin_rating wasn't relevant so I removed it for now. I edited the SQL to make it more clear, and thanks for points it out it should have been pi.proejct_id = p.id — Marc Zaharescu, Apr 28 '16 at 15:36

score 1 · Answer 1 · answered Apr 28 '16 at 16:28

Since you still did not provide any sql fiddle you can start from my one:

http://sqlfiddle.com/#!9/6c0569/1

SELECT   pc.country_id, pi.industry_id, 
  COUNT(p.id) AS num_projects,
  COUNT(u.id) AS num_consultants
  FROM   project p
INNER JOIN project_country pc
ON p.id = pc.project_id
INNER JOIN project_industry pi
ON pi.project_id=p.id
INNER JOIN `user` u
ON u.is_active = 1 AND u.type = 0 
   and u.country_id = pc.country_id
INNER JOIN industry_experience ie
ON u.id = ie.consultant_profile_id
  AND ie.industry_id = pi.industry_id
GROUP BY pc.country_id, pi.industry_id

if you will add some data into that fiddle we can discuss deeper

MySQL: JOIN multiple tables

1 Answers1