Mysql, Multiple Left Join - 3 Tables

Question

I have some issues with multiple joins in MysQL I have 3 tables:

cms_data_company
cms_data_company_categories
cms_datasrc_category

Sample records from: cms_data_company:

+----+-----------+------------+
| id | name      | address    |
+----+-----------+------------+
| 1  | Name1     | Samplestr1 |
+----+-----------+------------+
| 2  | Name2     | Samplestr2 |
+----+-----------+------------+
| 3  | Name3     | Samplestr3 |
+----+-----------+------------+

Sample records from: cms_data_company_categories ( It contains Company_id field and category_id ) Point is that there is serveral records for one company_id )

+----+-----------+------------+
| id | company_id| category_id|
+----+-----------+------------+
| 1  | 2         | 14         |
+----+-----------+------------+
| 2  | 2         | 11         |
+----+-----------+------------+
| 3  | 1         | 15         |
+----+-----------+------------+

Sample records from: cms_datasrc_category ( Here is a issue that i need only that rows where:

 datasrc = 1 AND parent = 0

+----+-----------+------------+-----------+
| id | datasrc   | parent     |name       |
+----+-----------+------------+-----------+
| 1  | 1         | 0          |category1  |
+----+-----------+------------+-----------+
| 2  | 2         | 0          |category2  |
+----+-----------+------------+-----------+
| 3  | 3         | 5          |category3  |
+----+-----------+------------+-----------+

What i would like to recive is that:

All fields from cms_data_company and field name from cms_datasrc_company

I need to join it as follows:

id from cms_data_company match with company_id from cms_data_company_categories
Then matched category_id from cms_data_company_categories with ID from cms_datasrc_category (only these records where datasrc=0 and parent=0)
Return name as new column with all field from cms_data_company

I think I could make it messy, but my MySQL statement is as follows:

select * ,cms_datasrc_category.name_en
from cms_data_company
LEFT JOIN cms_data_company_categories.company_id
ON cms_data_company.id = cms_data_company_categories.company_id 
LEFT JOIN cms_datasrc_category
ON cms_data_company_categories.category_id = cms_datasrc_category.id
WHERE cms_datasrc_category.datasrc = 1 AND cms_datasrc_category.parent = 0

It seems It is working somehow but, there is only records from cms_data_company where query can find something. I would like to change my statemat to show NULLs when There is no matching fields.

It is because WHERE applies to all Query ?

Gordon Linoff · Accepted Answer · 2016-06-10 12:04:35Z

3

When you use left joins, conditions on all but the first table should be in the on clauses:

SELECT *, cdc.name_en
FROM cms_data_company dc LEFT JOIN
     cms_data_company_categories.company_id c
     ON dc.id = c.company_id LEFT JOIN
     cms_datasrc_category cdc
     ON c.category_id = cdc.id AND
        cdc.datasrc = 1 AND cdc.parent = 0;

Notes:

Table aliases make a query easier to write and to read.
You should use table aliases for all column references, when your query has more than one table.
The select * already selects all columns from all tables. There is no need to include another column. Or, better yet, list the columns you really need.
The filtering conditions are on the last table, so they are now in the on clause.

answered Jun 10, 2016 at 12:04

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jadupl Over a year ago

Thank You for fast reply I've made an mistake on: LEFT JOIN cms_data_company_categories.company_id It should be: LEFT JOIN cms_data_company_categories But It is no problem now. Now there is issue with this: cms_data_company_categories contains column company_id where there is serveral categories_id for one company_id . Now it returns all of records it founds. I would like to return, that categories_id which in table cms_datasrc_category Has datasrc = 1 AND parent = 0

Collectives™ on Stack Overflow

Mysql, Multiple Left Join - 3 Tables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related