Select when group by contains more than one row with the same value?

Question

I am trying to write a query that returns one row from table A whenever a column in joined table B contains multiple distinct values for a single matching row in table A. (A -> B is a 1 -> many relationship.) I built a SQL fiddle to demonstrate this in context: http://sqlfiddle.com/#!6/83952/1

In this fiddle, the design column of the perf_ticket_type table should be the same for every ticket_type that has the same perf_id, but I'm trying to select only the instances where it doesn't. So for perf_id 3, there is more than one unique design being returned with the query I'm using currently.

What I want as my result is the two columns of the performance table only for perf_id 3, based on the multiple values of design for that perf_id in the joined table.

I've been frustrated by understanding GROUP BY in the past, so I'm unsure if there's something different I could be doing here to get my desired result. At the moment, I think I can select what I have in the fiddle into a temp table and then do another select on that with a GROUP BY perf_id HAVING COUNT(*) > 1 to get what I want (as per select rows where column contains same data in more than one record), but that seems like it's an extra step.

Rob Farley · Accepted Answer · 2015-08-31T03:20:23.630

Your idea is right but you need HAVING COUNT(DISTINCT design) > 1

Like this:

WITH multi_design_perfs AS
(SELECT b.perf_id 
FROM perf_ticket_type b
GROUP BY b.perf_id
HAVING COUNT(DISTINCT b.design) > 1
)
SELECT m.perf_id, 
    STUFF((select ', ' + CAST(b.design AS varchar(10))
     FROM perf_ticket_type b 
     WHERE b.perf_id = m.perf_id
     ORDER BY b.design
     FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,2,'')
FROM multi_design_perfs m
;

Select when group by contains more than one row with the same value?

1 Answers1