CODEWITHSUNDEEP
mysql
Abhi
Abhi

Reputation: 4261

MySQL GROUP_CONCAT having all values same

I have the following query:

SELECT * FROM charges WHERE (
  charges.id not in (
    select charge_id from billing_invoice_charges where is_deactivated = 0
  )
)

I need to convert it into a JOIN QUERY, so I'm trying:

SELECT charges.id, group_concat(bic.is_deactivated) AS active_statuses 
FROM charges LEFT JOIN billing_invoice_charges AS bic
ON bic.charge_id = charges.id GROUP BY charges.id

HAVING .......; <--- Check if all values are 1's

The output of GROUP_CONCAT is:

+------+-----------------+
| id   | active_statuses |
+------+-----------------+
|    2 | 0,1,1           |
|    3 | 1,1             |
|    6 | 1               |
|    7 | 1,1,1           |
|   12 | 0,0,1           |
+------------------------+

How can I check if all the values if active_statuses in HAVING clause are 1's? This should give me the charges I'm looking for.

Upvotes: 0

Views: 856

Answers (2)

Dymen1
Dymen1

Reputation: 736

Try:

SELECT
    charges.id,
    GROUP_CONCAT(bic.is_deactivated) AS active_statuses 
FROM charges
LEFT JOIN billing_invoice_charges AS bic
ON bic.charge_id = charges.id
GROUP BY charges.id
HAVING active_statuses NOT LIKE '%0%'

which should ignore any results with a 0 in active_statuses

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520978

Try a HAVING clause which uses conditional aggregation to ensure that no non 1 statuses occur for each group.

SELECT
    charges.id,
    GROUP_CONCAT(bic.is_deactivated) AS active_statuses 
FROM charges
LEFT JOIN billing_invoice_charges AS bic
    ON bic.charge_id = charges.id
GROUP BY charges.id
HAVING SUM(CASE WHEN bic.is_deactivated <> 1 THEN 1 ELSE 0 END) = 0

Upvotes: 4

Related Questions