5

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.

4

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
;
  • Oh hey, that CTE seems like a great way to avoid the temp table. And originally I had no idea what you were doing with STUFF() and XML, but now that I consider it, that's a fascinating solution that could work for an entirely different project I have. Thanks! – NReilingh Aug 30 '15 at 22:55
  • STUFF() is just to remove the leading comma. The FOR XML PATH bit does the concatenation. – Rob Farley Aug 30 '15 at 23:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.