Really Tough Query - Please Help

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Really Tough Query - Please Help

Author

Topic

StephanieJones
Starting Member

12 Posts

Posted - 2004-05-10 : 23:57:55

Hi,

You helped me a few days ago work out this extremely difficult (for me) query. Now I need to add one filter to this thing. I only want records returned that have the string "yes" in the IssueStatus field.

I tried hard to dissect what you did for me and I could not make it happen. Here is the original:

SELECT x.ProjectID, x.ProjectCode, x.ProjectName, y.Status AS MaxDepStatus, z.Status AS MaxTestStatus, xx.Status AS MaxReqStatus,
yy.Status AS MaxSuppSiteStatus, zz.Status AS MaxSECStatus
FROM (SELECT a.ProjectID, a.ProjectCode, a.ProjectName, MAX(b.DStatusID) 'MaxDepStat', MAX(c.TStatusID) 'MaxTestStat', MAX(d .RStatusID)
'MaxReqStat', MAX(e.SStatusID) 'MaxSuppSiteStatus', MAX(f.SECStatusID) 'MaxSECStatus'
FROM PROJECTS a LEFT JOIN
DEPLOYMENT b ON a.ProjectID = b.fProjectID LEFT JOIN
TESTING c ON a.ProjectID = c.fProjectID LEFT JOIN
Requirements d ON a.ProjectID = d .fProjectID LEFT JOIN
SitePrep e ON a.ProjectID = e.fProjectID LEFT JOIN
SECURITY f ON a.ProjectID = f.fProjectID

GROUP BY a.ProjectID, a.ProjectCode, a.ProjectName) x LEFT OUTER JOIN
PROJECTSTATUS y ON x.MaxDepStat = y.StatusID LEFT OUTER JOIN
PROJECTSTATUS z ON x.MaxTestStat = z.StatusID LEFT OUTER JOIN
PROJECTSTATUS xx ON x.MaxReqStat = xx.StatusID LEFT OUTER JOIN
PROJECTSTATUS yy ON x.MaxSuppSiteStatus = yy.StatusID LEFT OUTER JOIN
PROJECTSTATUS zz ON x.MaxSECStatus = zz.StatusID
ORDER BY x.ProjectCode

If I was writing a simple query like this, I would write:

Select fProjectID, Max(DStatusID) FROM DEPLOYMENT WHERE IssueStatus
= 'Yes'
I need this for each of these subqueries

Would you mind helping me one more time?

Thanks,

Stephanie Jones

Stephanie Jones

lauramayer
Posting Yak Master

152 Posts

Posted - 2004-05-11 : 06:15:21

Hi Stephanie,

Forgive me in advance, I'm asking the obvious here, have you tried putting the where clause in your query? The where clause comes before the group by, so it would look something like this:

.....
TESTING c ON a.ProjectID = c.fProjectID LEFT JOIN
Requirements d ON a.ProjectID = d .fProjectID LEFT JOIN
SitePrep e ON a.ProjectID = e.fProjectID LEFT JOIN
SECURITY f ON a.ProjectID = f.fProjectID

WHERE b.IssueStatus = 'Yes'

GROUP BY a.ProjectID, a.ProjectCode, a.ProjectName) x LEFT OUTER JOIN
PROJECTSTATUS y ON x.MaxDepStat

Laura

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-11 : 07:51:55

double post

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

StephanieJones
Starting Member

12 Posts

Posted - 2004-05-11 : 09:57:15

Laura,

Your question was not crazy at all! I was the crazy one for not trying it! I knew that I needed the Where clause in there but I was so intimidated with the query that I afraid of touching it. The guy that wrote that query for me was on this site and he did an excellent job, but I forgot that I wanted to filter the information.

That one query has taught me a wealth of information - alias table names and using tables as in the Select statement, among other things.

This site is one the most USEFUL and HELPFUL sites I have encountered with any issue and I really appreciate your help.

My question was answered in another posting on this site, but thanks so much for being willing to help!

Stephanie

Stephanie Jones

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources