[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Result Set

Audrey Ng

3/23/2007 3:37:00 PM

Hi everyone,

I have a main Company table with let's say:
Company A,B,C,D,E

I have a view with data allocated to Company A,B,C

However, I want my result set to show Company A,B,C,D,E
where D,E will also be displayed but showing 0 values
as opposed to not be displayed at all in the result set.

SELECT
c.strorganizationname as CompanyGroup,
SUM(Amount) as Amount
FROM Company a
LEFT JOIN vw_activity b on a.companyid = b.companyid
WHERE refdate BETWEEN '03-01-2007' AND '03-22-2007'
and c.companygroupid = 15

** Companygroupid includes Company A,B,C,D,E

*** Sent via Developersdex http://www.develop... ***
5 Answers

Plamen Ratchev

3/23/2007 4:06:00 PM

0

If you have Company D and E in the Company table, wouldn't the LEFT join
return those in the result set? If that is the case then you only need a
CASE in the SUM to check if D and E to make the Amount 0 (or if Amount comes
from the view, then just use COALESCE inside the SUM to make the NULL 0).

In case D and E are not in the Company table, then you can use UNION ALL,
like:

<your query>
UNION ALL
SELECT 'D', 0
UNION ALL
SELECT 'E', 0

HTH,

Plamen Ratchev
http://www.SQL...

Audrey Ng

3/23/2007 4:28:00 PM

0

That's what I thought too. I thought the LEFT JOIN would be enough but
I'm only getting the result set of Company A,B,C. Company D,E is
omitted.

I also tried putting SUM(COALESCE(Amount,0) since the Amount is coming
from the view and it still didn't work.

Any more suggestions?







*** Sent via Developersdex http://www.develop... ***

Audrey Ng

3/23/2007 4:30:00 PM

0

I should be more clear on this statement:

Here it is again:

SELECT
c.strorganizationname as CompanyGroup,
SUM(COALKSCE(b.Amount,0)) as Amount
FROM Company c
LEFT JOIN vw_activity b on c.companyid = b.companyid
WHERE b.refdate BETWEEN '03-01-2007' AND '03-22-2007'
and c.companygroupid = 15


*** Sent via Developersdex http://www.develop... ***

Audrey Ng

3/23/2007 4:33:00 PM

0


I got it to work! Thanks!

The refdate filter in the where clause should be in the left join


*** Sent via Developersdex http://www.develop... ***

Plamen Ratchev

3/23/2007 4:49:00 PM

0

Yes, this is what I was going to say, most likely your WHERE filters out
those rows...

Plamen Ratchev
http://www.SQL...