Return to Answer

deleted 28 characters in body

edited May 13, 2018 at 11:38

1.3k
2
16
29

For the record, this is what I have come up with:

WITH event_attribute AS ( SELECT event_attribute.event_id, event_attribute.attribute FROM ( VALUES (1, '2D'), (1, 'IMAX'), (2, 'IMAX'), (3, '3D') ) event_attribute (event_id, attribute) ), append_attribute_event AS ( SELECT event_id FROM event_attribute GROUP BY event_id HAVING COUNT(*) > 0 AND MAX(CASE "attribute" WHEN '2D' THEN 1 ELSE 0 END) = 0 AND MAX(CASE "attribute" WHEN '3D' THEN 1 ELSE 0 END) = 0 ) SELECT * FROM event_attribute UNION ALL SELECT event_id, '2D' "attribute" FROM append_attribute_event

However, I am pretty sure Gerard's suggestion is better.

For the record, this is what I have come up with:

WITH event_attribute AS ( SELECT event_attribute.event_id, event_attribute.attribute FROM ( VALUES (1, '2D'), (1, 'IMAX'), (2, 'IMAX'), (3, '3D') ) event_attribute (event_id, attribute) ), append_attribute_event AS ( SELECT event_id FROM event_attribute GROUP BY event_id HAVING COUNT(*) > 0 AND MAX(CASE "attribute" WHEN '2D' THEN 1 ELSE 0 END) = 0 AND MAX(CASE "attribute" WHEN '3D' THEN 1 ELSE 0 END) = 0 ) SELECT * FROM event_attribute UNION ALL SELECT event_id, '2D' "attribute" FROM append_attribute_event

However, I am pretty sure Gerard's suggestion is better.

For the record, this is what I have come up with:

WITH event_attribute AS ( SELECT event_attribute.event_id, event_attribute.attribute FROM ( VALUES (1, '2D'), (1, 'IMAX'), (2, 'IMAX'), (3, '3D') ) event_attribute (event_id, attribute) ), append_attribute_event AS ( SELECT event_id FROM event_attribute GROUP BY event_id HAVING MAX(CASE "attribute" WHEN '2D' THEN 1 ELSE 0 END) = 0 AND MAX(CASE "attribute" WHEN '3D' THEN 1 ELSE 0 END) = 0 ) SELECT * FROM event_attribute UNION ALL SELECT event_id, '2D' "attribute" FROM append_attribute_event

However, I am pretty sure Gerard's suggestion is better.

Source Link

answered May 13, 2018 at 11:23

Gajus

1.3k
2
16
29

For the record, this is what I have come up with:

WITH event_attribute AS ( SELECT event_attribute.event_id, event_attribute.attribute FROM ( VALUES (1, '2D'), (1, 'IMAX'), (2, 'IMAX'), (3, '3D') ) event_attribute (event_id, attribute) ), append_attribute_event AS ( SELECT event_id FROM event_attribute GROUP BY event_id HAVING COUNT(*) > 0 AND MAX(CASE "attribute" WHEN '2D' THEN 1 ELSE 0 END) = 0 AND MAX(CASE "attribute" WHEN '3D' THEN 1 ELSE 0 END) = 0 ) SELECT * FROM event_attribute UNION ALL SELECT event_id, '2D' "attribute" FROM append_attribute_event

However, I am pretty sure Gerard's suggestion is better.