Counting employees in business with branches

Question

Select the group in which the Primary is located, and, for example, 5 Branch . So, the total number of seats is 6 . In each of these 6 find 3 workers who work as job_types LIKE "%C%" . If in one of these 6 places there are 3 workers with a given parameter, the query should get the results of all these 6 places.

To clarify: 3 workers should work in one main / branch.

Since the project itself is very dense, it would be better to get the results using the RAW query:

business table

 id | mainorbranch | name -------------------------------------- 1 Main Apple 2 Branch Apple London 3 Branch Apple Manchester 4 Main IBM 5 Branch IBM London etc ... 

Relations

business_branches table

 b_id | branch_id | id -------------------------------------- 1 1 1 2 2 1 3 3 1 4 4 4 5 5 4 // etc 

people_details table

 d_id | id | job_types -------------------------------------- 1 1 C 2 3 D 3 2 F 4 4 C 5 5 C // etc 

people_branches table

 pb_id | branch_id | id -------------------------------------- 1 1 3 2 3 2 3 4 4 4 2 5 5 1 1 // etc 

What I need to get:

 Business id | Name | Postcode ----------------------------------------- 1 Apple postcode 2 Apple 232 postcode 3 Apple 323 postcode // etc... 

---

DB structure for helpers http://sqlfiddle.com/#!9/206733

Simplified, reduced SQL file with 110k + total rows

UPDATE

@KikiTheOne's answer to this question, but it only gets half the result. The other half is missing.

Answer 1

as discussed in the chat. here is the solution:

if u Need Company Infos ... get them @ t1.XXXX as postcode .

i changed

 "pb_id" "branch_id" "id" "1" "1" "3" "2" "3" "2" "3" "1" "4" "4" "1" "5" "5" "1" "1" 

so I get 3 people in 1 branch

 SELECT t1.id as "Business id", t1.name as Name, 'postcode' as "Postcode" FROM SO_business as t1 inner join ( SELECT * FROM SO_busness_branches as t3 inner join ( SELECT t5.branch_id as inner_branch, count(t5.branch_id) as workers_in, max(t6.job_types) as job_types, max(t7.id) as mainbranch FROM SO_people_branches as t5 inner join SO_people_details as t6 on t5.id = t6.id inner join SO_busness_branches as t7 on t5.branch_id = t7.branch_id WHERE t6.job_types LIKE '%C%' GROUP BY t5.branch_id ) as t4 on t3.id = t4.inner_branch WHERE t4.workers_in >= 3 ) as t2 on t1.id = t2.branch_id 

Explanation:

-. 1 The innermost SQL counts ALL branches with workers (number of workers init) and Job_type =% c% and connects the MAIN id of the branch.

-. 2 second SQL receives this information and selects only all branches with working> = 3

-. 3 external SQL selects all internal INFOS and returns ALL branches / main using branchID-Main from Internal SQL. And connects them to the Business table so that you can display all access to this message.

Answer 2

hope this works, as it is not so easy to interpret the relationship with the column name in your question.

With this script you can add any group of main / branches to search in

 SELECT b.id, b.name, 'postcode' as postcode FROM business b INNER JOIN business_branches bb ON (bb.branch_id = b.id) WHERE bb.id IN ( SELECT bb1.id FROM people_details pd INNER JOIN people_branches pb ON (pb.id = pd.id) INNER JOIN business_branches bb1 ON (bb1.branch_id = pb.branch_id) INNER JOIN business b1 ON (b1.id = bb1.branch_id) WHERE pd.job_types like '%C%' AND bb1.id IN (1,4) -- You can add as many group of businesses (main/branch combinations) using the main branch key GROUP BY pb.branch_id HAVING count(pb.branch_id) >= 3 )