0

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

enter image description here

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.

Improve this question
1
  • 2
    Show us the indexes, preferably via SHOW CREATE TABLE.
    – Rick James
    Commented May 21, 2019 at 4:58

1 Answer 1

Reset to default
0

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>

Improve this answer
9
  • Thanks a lot. But still no result from the query it becomes unresponsive after several minutes. I could use INNER JOIN, no problem on that. Does the ( ) change something specific, or did you make any other change that i'm missing since the query looks pretty much the same...
    – gallo2000sv
    Commented May 20, 2019 at 23:37
  • @gallo2000sv Try splitting the joins into VIEWs or add a LIMIT clause for the first 10 results, just to see if it works at all.
    – leeand00
    Commented May 21, 2019 at 0:37
  • @gallo2000sv The () group together an inner join statement so that it's like one table, or one query, or one view.
    – leeand00
    Commented May 21, 2019 at 0:42
  • You should probably change your fields to a count(*) so you can see how many records would be returned. The query is likely timing out before it returns. I didn't actually test it out, I took your code and made a guess based on the ERD you uploaded.
    – leeand00
    Commented May 21, 2019 at 0:44
  • In MySQL, INNER JOIN is identical to JOIN. And I doubt if the parentheses make any difference in this case.
    – Rick James
    Commented May 21, 2019 at 4:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.