CODEWITHSUNDEEP
mysqlsqldatabase
luis.ap.uyen
luis.ap.uyen

Reputation: 1364

Get records from a table A related to a table B, which has children records in the same table

I have this situation:

A table called "services" and another one "companies":

services
------------
id
code
companies_id

companies
------------
id
parent_id

Sample records:

services
---------------
id     code    companies_id
1      s1      1
2      s2      1
3      s3      2
4      s4      2
5      s5      3
6      s6      3

companies
---------------
id    parent_id
1     0
2     1
3     0

services relate to companies with companies_id. So that a company may have 0 to N services.

A company may have children companies, so that these ones relate to their parent with parent_id.

What I need is a query to get any record in services related to a given company (id:1) and its children companies.

I should get this (the services of company 1 and its children company 2):

services
------------------
id   code  companies_id
1      s1      1
2      s2      1
3      s3      2
4      s4      2

This is my try, but I don't seem to get the correct number of rows:

SELECT * 
FROM services
INNER JOIN companies ON services.companies_id = companies.id 
LEFT JOIN companies as children_companies ON services.companies_id = children_companies.id 
WHERE (companies.id =1 OR children_companies.parent_id = 1)

Any suggestion please?

Upvotes: 0

Views: 31

Answers (1)

isaace
isaace

Reputation: 3429

This should work

select b.id, b.code, b.companies_id from companies a
join services b on a.id = b.companies_id
where b.companies_id = 1 or a.id in 
(select id from companies c where c.parent_id = 1)

Upvotes: 1

Related Questions