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