CODEWITHSUNDEEP
mysqlsql
Priyanshu Kumawat
Priyanshu Kumawat

Reputation: 51

Where condition in GROUP_CONCAT

enter image description here

I'm trying to get the Image IDs in which the label 'car' is not present. I've tried to group all the object labels which is related to specific Image ID.

I'm trying to exclude the Image ID in which the 'car' is present. But instead of this only object label 'car' is getting excluded in the group.

I've tried this -

SELECT used_car_image_id
   , GROUP_CONCAT(DISTINCT object_label ORDER BY object_label ASC SEPARATOR ',')
FROM individual_used_cars_image_label 
GROUP BY used_car_image_id
HAVING GROUP_CONCAT(DISTINCT object_label 
ORDER BY object_label ASC SEPARATOR ',') != 'car'

And this -

SELECT used_car_image_id
   , GROUP_CONCAT(DISTINCT object_label ORDER BY object_label ASC SEPARATOR ',')
FROM individual_used_cars_image_label 
WHERE object_label !='car'
GROUP BY used_car_image_id

Both queries are giving the ID and just excluding the object label 'car'. The output should be the row of Image IDs in which there's no object label named 'car'

Upvotes: 0

Views: 539

Answers (3)

Nick
Nick

Reputation: 147166

You could just add a count of how many of the object_label values associated with each used_car_image_id were equal to car and use that to filter the results. This query takes advantage of the fact that MySQL treats boolean expressions as 1 (true) or 0 (false) in a numeric context.

SELECT used_car_image_id
     , GROUP_CONCAT(DISTINCT object_label ORDER BY object_label ASC SEPARATOR ',')
FROM individual_used_cars_image_label 
GROUP BY used_car_image_id
HAVING SUM(object_label = 'car') = 0

Demo on dbfiddle

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

You should be using a CASE expression in the HAVING clause which checks for the presence of car:

SELECT used_car_image_id
     , GROUP_CONCAT(DISTINCT object_label ORDER BY object_label SEPARATOR ',') labels
FROM individual_used_cars_image_label 
GROUP BY used_car_image_id
HAVING COUNT(CASE WHEN object_label = 'car' THEN 1 END) = 0;

Upvotes: 0

forpas
forpas

Reputation: 164089

You can easily exclude the Image IDs with label 'car' with NOT EXISTS:

SELECT 
  t.used_car_image_id, 
  GROUP_CONCAT(DISTINCT t.object_label ORDER BY t.object_label ASC SEPARATOR ',') labels
FROM individual_used_cars_image_label t 
WHERE NOT EXISTS (
  SELECT 1 FROM individual_used_cars_image_label
  WHERE used_car_image_id = t.used_car_image_id AND object_label ='car' 
)
GROUP BY t.used_car_image_id

Upvotes: 0

Related Questions