Get results on the account and operators in one request

Question

I think the question is wrong, but I don’t know how to ask correctly.

This query selects all enterprises with all employees whose job_types contains a C letter.

 $connect = DB::table('relationship') ->join('workers', 'workers.id', '=', 'relationship.w_id') ->join('business', 'business.id', '=', 'relationship.b_id') ->whereRaw('job_types LIKE "%C%"') ->groupBy('relationship.w_id') ->get(); 

I use foreach to display results

 foreach ($connect as $item) { echo $item->name; // etc } 

I want to select all enterprises that have more than 3 or less than 3 or equal to 3 (depending on what I need) job_types LIKE "%C%" and store the information as follows:

 1. APPLE | Tom | C 2. APPLE | Tim | C 3. APPLE | Jeff | C 4. IBM | Jenny | C 5. IBM | Sean | C 6. IBM | Ian | C // etc`` 

The answer is @KikiTheOne , but it does not display the results as needed.

Answer 1

***** ***** DECISION

 SELECT * FROM people_details as t1 inner join people_branches as t2 on t1.id = t2.id inner join ( SELECT count(t1.id) as worker_counter,t1.branch_id FROM people_branches as t1 inner join people_details as t2 on t1.id = t2.id WHERE t2.job_types LIKE '%C%' group by branch_id ) as t3 on t2.branch_id = t3.branch_id inner join people_frontpage as t4 on t4.id = t1.id inner join business as t5 on t5.id = t2.branch_id WHERE t1.job_types LIKE '%C%' AND t3.worker_counter > 200 

----------

---

OLD - UPDATE

• 
• 
• 
• 

---

 SELECT t3.bus_name, t1.name, t1.job_types FROM SO_WORKER as t1 inner join SO_RELATIONSHIP as t2 on t1.id = t2.w_id inner join ( SELECT count(t1.w_id) as worker_counter,t1.b_id,t3.bus_name FROM SO_RELATIONSHIP as t1 inner join SO_WORKER as t2 on t1.w_id = t2.id inner join SO_BUSINESS as t3 on t3.id = t1.b_id WHERE t2.job_types LIKE '%C%' group by b_id ) as t3 on t2.b_id = t3.b_id WHERE t1.job_types LIKE '%C%' AND t3.worker_counter <= 3 

Unformated

 SELECT t3.bus_name, t1.name, t1.job_types FROM SO_WORKER as t1 inner join SO_RELATIONSHIP as t2 on t1.id = t2.w_id inner join (SELECT count(t1.w_id) as worker_counter,t1.b_id,t3.bus_name FROM SO_RELATIONSHIP as t1 inner join SO_WORKER as t2 on t1.w_id = t2.id inner join SO_BUSINESS as t3 on t3.id = t1.b_id WHERE t2.job_types LIKE '%C%' group by b_id) as t3 on t2.b_id = t3.b_id WHERE t1.job_types LIKE '%C%' AND t3.worker_counter <= 3 

----------------------------------------------- ---

OLD CODE

regarding the comments from message 1.

 Table: SO_BUSINESS id | bus_name -------------------- 1 | BUSI A 2 | BUSI B Table: SO_WORKER id | job_types --------------------- 1 | CEO 2 | GFO 3 | CTO 4 | Manager 5 | Worker Table: SO_RELATIONSHIP w_id | b_id ---------------- 1 | 1 2 | 2 3 | 1 4 | 1 5 | 2 Query: Output workers_count | b_id | bus_name -------------------------------------------- 2 | 1 | BUSI A 

.

 SELECT * FROM ( SELECT count(t1.w_id) as workers_count, t1.b_id, t3.bus_name FROM SO_RELATIONSHIP as t1 inner join SO_WORKER as t2 on t1.w_id = t2.id inner join SO_BUSINESS as t3 on t1.b_id = t3.id WHERE t2.job_types LIKE '%C%' GROUP BY t1.b_id ) as t4 WHERE t4.workers_count < 3 

The code is unformatted:

 SELECT * FROM (SELECT count(t1.w_id) as workers_count,t1.b_id,t3.bus_name FROM SO_RELATIONSHIP as t1 inner join SO_WORKER as t2 on t1.w_id = t2.id inner join SO_BUSINESS as t3 on t1.b_id = t3.id WHERE t2.job_types LIKE '%C%' GROUP BY t1.b_id) as t4 WHERE t4.workers_count < 3 

Let me know if this helps u

Answer 2

 $connect = DB::table('relationship') ->join('workers', 'workers.id', '=', 'relationship.w_id') ->join('business', 'business.id', '=', 'relationship.b_id') ->selectRaw('workers.*,business.*,(select count(*) from workers where job_types like "%c%") as workers_count') ->where('job_types', 'like', '%C%') ->having('workers_count','>=',5) ->groupBy('relationship.w_id') ->get();