New SQL Query Issue...

Hello all,

Well I guess with any good application making comes SQL query problems as one trys to out-do what they did before. In any case, I am having an issue with sorting again, but this time its for a new field.

I have three tables, "Projects", "Users", and "Groups". Now the problem is this... a user can own a project, or a user could belong to a group, which owns the project. Whatever the case, a project will be owned by EITHER a group, or user, not both. So, when looping my Projects recordset, I have a second recordset that figures out if the GroupID is null in the Projects table, and if it is, it looks up the User's name in the Users table. If the GroupID isn't null, it grabs the Group name from the Group's table.

The problem is, I want to be able to do nice sorting with only one recordset? Is that possible?

ID Project Title Owner
1 Test Project XYZ Enterprises, Inc.
2 Another Project John Doe
3 Something Else Microsoft, Inc.

Thanks again!
TylerYou should be able to gather all of the data using one query and a CASE statement. I am assuming you have a UserID in your Projects table:

SELECT
 Projects.ID,
 Projects.Title,
 CASE
 WHEN Projects.GroupID IS NOT NULL THEN Groups.Name
 ELSE
 Users.Name
 END AS Owner
FROM
 Projects
LEFT OUTER JOIN
 Groups ON Projects.GroupID = Groups.GroupID
LEFT OUTER JOIN
 Users ON Projects.UserID = Users.UserID
ORDER BY
 Projects.ID

Terri|||I will give that a shot! Thanks for introducing me to new SQL keywords. Do you have a site that I could resource to learn more about making complicated queries? It seems like I have to make them more and more.

Also, I have a third issue. In addition to the "Projects" and "Tasks" table that I'm sure your all too familiar with, I have a third level down called "Hours"... Now, this is the most complicated thing ever, so I doubt it is possible. But, I want to be able to pull out the earliest StartDate in the Hours table, and the last EndDate in the Hours table. And the hours table related to the "Tasks" table with the TaskID in both tables, and the "Tasks" relate to the "Projects" with the ProjectID in both tables. So, basically I want to look the Projects recordset, showing the Start and End dates for that project. Is that possible? It would have to be the highest and lowest dates. Not sure how to do all that action, especially while keeping the COALEASE thing, and this thing above!!

Thanks again,
B|||Ok, so my new SQL statement works, and looks like this so far:

SELECT Projects.*, Managers.LastName AS ManagersLastName, Managers.FirstName AS ManagersFirstName,
 ROUND(COALESCE (SUM(Tasks.TaskStatusPercent) / COUNT(Tasks.TaskID), 0), 0) AS Status, CASE WHEN Projects.GroupID IS NOT NULL
 THEN Groups.GroupName ELSE Users.FirstName END AS Client
FROM Projects INNER JOIN
 Users Managers ON Projects.ManagerID = Managers.UserID LEFT OUTER JOIN
 Users ON Projects.UserID = Users.UserID LEFT OUTER JOIN
 Groups ON Projects.GroupID = Groups.GroupID LEFT OUTER JOIN
 Tasks ON Tasks.ProjectID = Projects.ProjectID
GROUP BY Projects.ProjectID, Projects.UserID, Projects.GroupID, Projects.ManagerID, Projects.StatusID, Projects.StatusPercent, Projects.ProjectTitle,
 Projects.ProjectURL, Projects.ProjectDescription, Projects.DateStarted, Projects.DateEnded, Managers.LastName, Managers.FirstName,
 Users.FirstName, Groups.GroupName

The only problem is, instead of having just Users.FirstName, I would like the "Client" field to be "Users.LastName, Users.FirstName", with the comma and space in there. Can I do that in SQL too?

Also, if you could figure out the issue with the Hours.StartDate and Hours.EndDate, that would be the last thing I would need!

Thanks again,
B|||Ok, so I found out if you do...

RTrim(Users.LastName) + ', ' + LTrim(Users.FirstName) END AS Client

... it will work. Now, I just have to figure out that StartDate, EndDate issue.

- B|||Woo-hoo, you're almost there! I believe all you need for the StartDate and EndDate are a MIN and a MAX in your column list, and a LEFT OUTER JOIN to the Hours table.

SELECT Projects.*, Managers.LastName AS ManagersLastName, Managers.FirstName AS ManagersFirstName,
 ROUND(COALESCE (SUM(Tasks.TaskStatusPercent) / COUNT(Tasks.TaskID), 0), 0) AS Status, CASE WHEN Projects.GroupID IS NOT NULL
 THEN Groups.GroupName ELSE RTrim(Users.LastName) + ', ' + LTrim(Users.FirstName) END AS Client,
 MIN(Hours.StartDate) AS StartDate,
 MAX(Hours.EndDate) AS EndDate
FROM Projects INNER JOIN
 Users Managers ON Projects.ManagerID = Managers.UserID LEFT OUTER JOIN
 Users ON Projects.UserID = Users.UserID LEFT OUTER JOIN
 Groups ON Projects.GroupID = Groups.GroupID LEFT OUTER JOIN
 Tasks ON Tasks.ProjectID = Projects.ProjectID LEFT OUTER JOIN
 Hours ON Tasks.TaskID = Hours.TaskID
GROUP BY Projects.ProjectID, Projects.UserID, Projects.GroupID, Projects.ManagerID, Projects.StatusID, Projects.StatusPercent, Projects.ProjectTitle,
 Projects.ProjectURL, Projects.ProjectDescription, Projects.DateStarted, Projects.DateEnded, Managers.LastName, Managers.FirstName,
 Users.FirstName, Groups.GroupName

Terri