Retrieve column from select inside SQL Statement 1

[JimmyFo]

Hi, I have a select statement that gets data from one table and I have part of the main select constructed of selects on another table to get counts and return it in the same data set.

    SELECT 
		P.intProjectID [ProjectID],
		P.strProjectName [Name],
		...
		COUNT_GOOD = (SELECT COUNT(*) [Good] FROM tblProjectMilestone WHERE
			--int_fk_ProjectID = @p_PROJECTID
			strMilestoneStatus = 'GOOD'),
		COUNT_CAUTION = (SELECT COUNT(*) [Good] FROM tblProjectMilestone WHERE
			--int_fk_ProjectID = @p_PROJECTID
			strMilestoneStatus = 'CAUTION'),
		COUNT_DANGER = (SELECT COUNT(*) [Good] FROM tblProjectMilestone WHERE
			--int_fk_ProjectID = @p_PROJECTID
			strMilestoneStatus = 'DANGER')
--select the resources too and dates
    FROM tblProject AS P ...

If you look at the commented out portions in each of the internal selects, I'd like to make the "@p_PROJECTID" equal to the value of the [ProjectID] I'm selecting just above it. Can I do this, or do I need a new statement?

Thanks,
James

 

[bborissov]

I prefer to do that in that way:

    [COLOR=blue]SELECT[/color]
        P.intProjectID [ProjectID],
        P.strProjectName [[COLOR=blue]Name[/color]],
        SUM([COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] Mls.strMilestoneStatus = [COLOR=red]'GOOD'[/color]    [COLOR=blue]THEN[/color] 1 [COLOR=blue]ELSE[/color] 0 [COLOR=blue]END[/color]) [COLOR=blue]AS[/color] Good,
        SUM([COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] Mls.strMilestoneStatus = [COLOR=red]'CAUTION'[/color] [COLOR=blue]THEN[/color] 1 [COLOR=blue]ELSE[/color] 0 [COLOR=blue]END[/color]) [COLOR=blue]AS[/color] CAUTION,
        SUM([COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] Mls.strMilestoneStatus = [COLOR=red]'DANGER'[/color] [COLOR=blue]THEN[/color] 1 [COLOR=blue]ELSE[/color] 0 [COLOR=blue]END[/color])  [COLOR=blue]AS[/color] DANGER
[COLOR=green]--select the resources too and dates
[/color]    [COLOR=blue]FROM[/color] tblProject [COLOR=blue]AS[/color] P ...
         [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] tblProjectMilestone Mls [COLOR=blue]ON[/color]  P.intProjectID = Mls.int_fk_ProjectID

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[bborissov]

I have an error. I forgot GROUP BY vlause so put it in the end:

    [COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] P.intProjectID,
             P.strProjectName

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[JimmyFo]

Thanks Borislav, that works - however, it now only returns rows if there are items in these "status" columns - if they have null or are empty, I'd like a "0" returned, as if it summed up and found nothing and returned all rows - is this possible?

Thanks!
James

 

[bborissov]

Change the JOIN from INNER to LEFT and tell me if that is what you want

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[PWise]

SUM(CASE WHEN isnull(Mls.strMilestoneStatus,'') = '' THEN 1 ELSE 0 END)  AS Nulls

 

[JimmyFo]

The 'LEFT' change worked wonders! Thanks!