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.
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.