Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004
    Posts
    3

    Question Unanswered: tough query help

    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

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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;

  3. #3
    Join Date
    Jul 2004
    Posts
    3
    Thanks Tony. You're my hero
    It worked with one modification: ...CASE WHEN COUNT (DISTINCT...)

    Thanks again!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •