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

industry table have id column ?

Sadikhasan
– Sadikhasan

2016-04-28 13:36:03 +00:00
Commented Apr 28, 2016 at 13:36 — Sadikhasan
– Sadikhasan, Commented Apr 28, 2016 at 13:36
do you have column name called id in industry table

Mujahidh
– Mujahidh

2016-04-28 13:36:10 +00:00
Commented Apr 28, 2016 at 13:36 — Mujahidh
– Mujahidh, Commented Apr 28, 2016 at 13:36
can you please post your industry table columns?

Mujahidh
– Mujahidh

2016-04-28 13:37:01 +00:00
Commented Apr 28, 2016 at 13:37 — Mujahidh
– Mujahidh, Commented Apr 28, 2016 at 13:37
Can you post your industry table structure?

Sadikhasan
– Sadikhasan

2016-04-28 13:37:33 +00:00
Commented Apr 28, 2016 at 13:37 — Sadikhasan
– Sadikhasan, Commented Apr 28, 2016 at 13:37
I will edit the post with the columns from each table

Marc Zaharescu
– Marc Zaharescu

2016-04-28 13:37:47 +00:00
Commented Apr 28, 2016 at 13:37 — Marc Zaharescu
– Marc Zaharescu, Commented Apr 28, 2016 at 13:37

Alex · Accepted Answer · 2016-04-28 16:28:52Z

1

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

answered Apr 28, 2016 at 16:28

Alex

17.3k1 gold badge31 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL: JOIN multiple tables

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related