tough query help

DC Ross · #1 (**permalink**) 07-27-04, 15:33

Hey all,
I have a query that I need some help with. Here's what I have, so far it's pretty basic, I'll explain what I need...

Code:

SELECT t.TaskID, t.IWOwner, t.Title, p.ProductName, r.RegionName, c.ChannelName
FROM Tasks t
LEFT JOIN TaskProduct tp
	ON t.TaskID = tp.TaskID
LEFT JOIN Products p
	ON tp.ProductID = p.ProductID
LEFT JOIN TaskRegion tr
	ON t.TaskID = tr.TaskID
LEFT JOIN Regions r
	ON tr.RegionID = r.RegionID
LEFT JOIN TaskChannel tc
	ON t.TaskID = tc.TaskID
LEFT JOIN Channels c
	ON tc.ChannelID = c.ChannelID

Here's a snippet of the results that this is returning:

Code:

1000    guy1    Testing 123     InfoPath      Japan      Volume Licensing
1000    guy1    Testing 123     Office Pro    US ONLY    Volume Licensing
1001    guy2    Testing 1234    Access        Europe     Retail FPP
1001    guy2    Testing 1234    FrontPage     Europe     Retail FPP
1003    guy3    Testing 321     Office Pro    NULL       NULL

What I need to return is a bit more involved than this. I would like one result per TaskID, but if there is more than one ProductID, RegionID and/or ChannelID, instead of listing each one as a result, I need it to say "Multiple", otherwise, if there is only one ProductID, RegionID and/or ChannelID, I need it to return the corresponding name. To clarify here's the result set I would like returned:

Code:

1000    guy1    Testing 123    Multiple      Multiple    Volume Licensing
1001    guy2    Testing 1234   Multiple      Europe      Retail FPP
1003    guy3    Testing 321    Office Pro    NULL        NULL

I apologize if this a bit convoluted, let me know if you need clarification.

Thanks in advance,
-DC Ross

andrewst · #2 (**permalink**) 07-28-04, 06:46

Try ths:

Code:

SELECT t.TaskID, t.IWOwner, t.Title,
       CASE WHEN (COUNT DISTINCT p.productName) > 1 THEN 'Multiple'
            ELSE MIN(p.productName) END product,
       CASE WHEN (COUNT DISTINCT r.regionName) > 1 THEN 'Multiple'
            ELSE MIN(r.regionName) END region,
       CASE WHEN (COUNT DISTINCT c.channelName) > 1 THEN 'Multiple'
            ELSE MIN(c.channelName) END channel
FROM Tasks t
LEFT JOIN TaskProduct tp
	ON t.TaskID = tp.TaskID
LEFT JOIN Products p
	ON tp.ProductID = p.ProductID
LEFT JOIN TaskRegion tr
	ON t.TaskID = tr.TaskID
LEFT JOIN Regions r
	ON tr.RegionID = r.RegionID
LEFT JOIN TaskChannel tc
	ON t.TaskID = tc.TaskID
LEFT JOIN Channels c
	ON tc.ChannelID = c.ChannelID
GROUP BY t.TaskID, t.IWOwner, t.Title;

DC Ross · #3 (**permalink**) 07-28-04, 13:29

Thanks Tony. You're my hero

It worked with one modification: ...CASE WHEN COUNT (DISTINCT...)

Thanks again!

Internet Services

Web Development

Digital Marketing

Consumer Tech