I have two tables Contractor Users as cu & Parkings as pak. But when we search the parking name our contractor parking also get affected by this search
Contractor Users as cu
| id | name |
|---|---|
| 1 | Tom |
| 2 | Jerry |
| 3 | Jake |
Parkings as pak
| id | name | contractor_ids |
|---|---|---|
| 1 | parking_a | 1 |
| 2 | parking_b | 1,3 |
| 3 | parking_c | 1,2 |
Query before any search
SELECT cu.name, JSON_ARRAYAGG(pak.name) as user_parkings
FROM contractor_users as cu
LEFT JOIN parkings as pak ON FIND_IN_SET(cu.id, pak.contractor_ids)
WHERE cu.name IS NOT NULL
GROUP BY cu.id
Result of Above query
| name | user_parkings |
|---|---|
| Tom | ['parking_a','parking_b', 'parking_c'] |
| Jerry | ['parking_c'] |
| Jake | ['parking_b',] |
Add search parking name filter in WHERE clause
SELECT cu.name, JSON_ARRAYAGG(pak.name) as user_parkings
FROM contractor_users as cu
LEFT JOIN parkings as pak ON FIND_IN_SET(cu.id, pak.contractor_ids)
WHERE cu.name IS NOT NULL AND pak.name = 'parking_a'
GROUP BY cu.id
Result of Above query
| name | user_parkings |
|---|---|
| Tom | ['parking_a'] |
So here is the problem you see user_parkings column also filtered but what I want after adding the search
| name | user_parkings |
|---|---|
| Tom | ['parking_a','parking_b', 'parking_c'] |
CodePudding user response：
Add search parking name filter in WHERE clause
This is incorrect - you need to check the value presence after the aggregation, so the condition must be placed not into WHERE but into HAVING.
Test, for example, this:
SELECT cu.name, JSON_ARRAYAGG(pak.name) as user_parkings
FROM contractor_users as cu
LEFT JOIN parkings as pak ON FIND_IN_SET(cu.id, pak.contractor_ids)
WHERE cu.name IS NOT NULL
GROUP BY cu.id
HAVING MAX(pak.name = 'parking_a')