Mysql – JOIN in 4 tables gets phptheadmin unresponsive

join;MySQLphpmyadmin

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.

Best Answer

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>

Related Solutions

Mysql – How to match on a longer word using MySQL Full Text Search

Here is your original query

SELECT
    SQL_CALC_FOUND_ROWS company.*,
    MATCH(company.c_title) AGAINST('SEARCH TEXT') as nameScore,
    MATCH(category.tags) AGAINST('SEARCH TEXT') as tagScore
FROM company LEFT JOIN category ON company.c_cat_id = category.c_id
WHERE MATCH(company.c_title) AGAINST('SEARCH TEXT')
OR MATCH(category.tags) AGAINST('SEARCH TEXT')
ORDER BY nameScore DESC

You may want to try running the fulltext searching before doing the LEFT JOIN

SELECT
    SQL_CALC_FOUND_ROWS company.*,
    company.nameScore,category.tagScore
FROM
    (SELECT *,WHERE MATCH(c_title) AGAINST('SEARCH TEXT' IN BOOLEAN MODE) nameScore
    FROM company WHERE MATCH(c_title) AGAINST('SEARCH TEXT' IN BOOLEAN MODE)) company
    LEFT JOIN
    (SELECT *,MATCH(tags) AGAINST('SEARCH TEXT' IN BOOLEAN MODE) as tagScore
    FROM category WHERE MATCH(tags) AGAINST ('SEARCH TEXT' IN BOOLEAN MODE)) category
    ON company.c_cat_id = category.c_id
ORDER BY nameScore DESC;

As for the search string emlakçı, try using a wildcard character. In other words,

MATCH(tags) AGAINST ('emlak*' IN BOOLEAN MODE)

This may pick up more occurrences but should get emlak and similar strings

UPDATE 2012-07-30 18:01 EDT

I don't know your table structure. Assuming name is a field in category, try this:

SELECT
    company.name,category.name,COUNT(1) CategoryCount
FROM
    (SELECT *,WHERE MATCH(c_title) AGAINST('SEARCH TEXT' IN BOOLEAN MODE) nameScore
    FROM company WHERE MATCH(c_title) AGAINST('SEARCH TEXT' IN BOOLEAN MODE)) company
    LEFT JOIN
    (SELECT *,MATCH(tags) AGAINST('SEARCH TEXT' IN BOOLEAN MODE) as tagScore
    FROM category WHERE MATCH(tags) AGAINST ('SEARCH TEXT' IN BOOLEAN MODE)) category
    ON company.c_cat_id = category.c_id
GROUP BY company.name,category.name;

Mysql – How to get only those values that has related elements from other table

Do it as GROUP BY aggregation

select
    c.id, c.name,
    count(distinct co.country_id) as amount,
    count(distinct u.id) usercount
from countries as c
left join companies as co on co.country_id = c.id
left join users as u on u.company_id = co.id
group by c.id, c.name
order by amount desc;

Best Answer

Related Solutions

Mysql – How to match on a longer word using MySQL Full Text Search

UPDATE 2012-07-30 18:01 EDT

Mysql – How to get only those values that has related elements from other table

Related Question