JOIN in 4 tables gets phpmyadmin unresponsive

Question

I can´t manage to create a valid query that shows me 5 columns from these 4 tables:

As you can see the relationships that i'm trying to JOIN in the query are highlighted in differents colors.

The query i came out is:

SELECT c.CompanyName
, s.categoryname
, cprs.strenght 
, s.parentid
, ss.Risklevel
, ss.ColumnType

FROM company_details AS c
JOIN category_master AS s ON s.id = c.categoryid
JOIN company_strenght AS cprs ON cprs.companyid = c.id
JOIN subsector_detasils AS ss ON s.parentid = ss.subcategoryid

But server gets unresponsive until i have to kill the process. The issue is in the last line, if i remove it and only manage 3 tables i get a successful result but is not enough for what i want.

Problem description: The company_details table is a list of companies. The id field is an unique ID that each company has. We need to match that id with the companyid field in the company_strenght table so we can get the c.CompanyName, s.categoryname, cprs.strenght columns. All good so far.

Then we need to include in the results the category_master table which contains the category that each company belongs to. So we match s.id = c.categoryidfor that. Still no problem and i get a list of companies with the catergories they belong to and also their parent category id.

Finally here is the problem: Each company has a parent category which id is parentid in the category_master table (marked in green). This parentid field is the same than the subcategoryid field in the subsector_detasils table. We need to match s.parentid = ss.subcategoryid so we can get ss.Risklevel, ss.ColumnType columns in the results. But as i said, it's not working since phpmyadmin just stops working and i have to kill the process manually.

Is there any other way to get around this problem? I'm running out of ideas...

Thanks in advance.

Answer 1

It's likely something more like this,but I'm not sure if you want to use INNER JOINs or not:

SELECT c.CompanyName
, s.categoryname
, cprs.strenght 
, s.parentid
, ss.Risklevel
, ss.ColumnType

FROM (
       (
         company_details AS c INNER JOIN category_master AS s ON s.id = c.categoryid
       ) INNER JOIN company_strenght AS cprs ON cprs.companyid = c.id
     ) INNER JOIN subsector_detasils AS ss ON s.parentid = ss.subcategoryid

make sure you're sure it's what you want, and I would consider sorting it as well to make sure everything you're expecting is there...

Also, to make this more readable, you might consider using some pre-made VIEWs, using a CREATE <view-name> VIEW AS <one-inner-join>