Tauras Tauras - 1 month ago 18
MySQL Question

Count workers in business with branches

Select a group, where is one Main and for example

5
Branch. So, the total of places is
6
. In each of those
6
, search for
3
workers, who is working as
job_types LIKE "%C%"
. If, in one of those
6
places, are
3
workers with given parameter, query must get results of all those
6
places.

To clarify:
3
workers must be working in same main/branch.

Because project itself is very dificult, it would be better, to get results using 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 ...


Relationship

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, minified SQL file with total of 110k+ rows

UPDATE

Answer by @KikiTheOne is kinda working, but it gets only half a results. Other half is missing.

Answer

as discussed in Chat. here is a solution:

if u Need Company Infos... get them @ t1.XXXX like 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 Most inner SQL Counts ALL branches with workers ( number of workers init ) and Job_type = %c% and joines the MAIN id of the branch.

-.2 the second SQL gets that info and only selects all branches with workers >= 3

-.3 the outer SQL selects all inner INFOS and gives back ALL branches/main with the branchID-Main from the Inner SQL. AND connects them to the Business table so u can Display all Infos likepostcode from there