Problem in select
-
I have this query that suppose to show the first name and last name of users (workers) in a milestone where the names of the users should not be repeated For example if john smith is repeated two times it should be selected once in the display. I have written this statement but it has an error. SELECT Profile.FirstName, Profile.LastName FROM AssignedTo INNER JOIN Profile ON AssignedTo.Username = Profile.Username CROSS JOIN Milestone WHERE (Milestone.MilestoneID = @MilestoneID) AND (AssignedTo.ProjectID = @ProjectID) GROUP BY Profile.Username Note: we can distinguish between users by using Username Thank you in advance fro your corporation
-
I have this query that suppose to show the first name and last name of users (workers) in a milestone where the names of the users should not be repeated For example if john smith is repeated two times it should be selected once in the display. I have written this statement but it has an error. SELECT Profile.FirstName, Profile.LastName FROM AssignedTo INNER JOIN Profile ON AssignedTo.Username = Profile.Username CROSS JOIN Milestone WHERE (Milestone.MilestoneID = @MilestoneID) AND (AssignedTo.ProjectID = @ProjectID) GROUP BY Profile.Username Note: we can distinguish between users by using Username Thank you in advance fro your corporation
The problem there is the Group By clause. A Group by is for aggregation (e.g. Averages, Sums, etc.) and your first name and last name aren't part of an aggregation. Try adding a DISTINCT clause after your select. See if that helps.
SELECT DISTINCT
Profile.FirstName,
Profile.LastName
FROM
AssignedTo
INNER JOIN Profile ON AssignedTo.Username = Profile.Username
CROSS JOIN Milestone
WHERE
(Milestone.MilestoneID = @MilestoneID)
AND (AssignedTo.ProjectID = @ProjectID)
Dad always thought laughter was the best medicine, which I guess is why several of us died of tuberculosis. I can picture in my mind a world without war, a world without hate. And I can picture us attacking that world, because they'd never expect it.
-
The problem there is the Group By clause. A Group by is for aggregation (e.g. Averages, Sums, etc.) and your first name and last name aren't part of an aggregation. Try adding a DISTINCT clause after your select. See if that helps.
SELECT DISTINCT
Profile.FirstName,
Profile.LastName
FROM
AssignedTo
INNER JOIN Profile ON AssignedTo.Username = Profile.Username
CROSS JOIN Milestone
WHERE
(Milestone.MilestoneID = @MilestoneID)
AND (AssignedTo.ProjectID = @ProjectID)
Dad always thought laughter was the best medicine, which I guess is why several of us died of tuberculosis. I can picture in my mind a world without war, a world without hate. And I can picture us attacking that world, because they'd never expect it.
Just so you know the following is no different than the query you wrote. In fact, using a group by in some instances is faster than using a distinct (atleast I read that somewhere about Oracle, although it could have changed with a new release of the database).
SELECT Profile.FirstName, Profile.LastName
FROM AssignedTo
INNER JOIN Profile ON AssignedTo.Username = Profile.Username
CROSS JOIN Milestone
WHERE (Milestone.MilestoneID = @MilestoneID)
AND (AssignedTo.ProjectID = @ProjectID)
GROUP BY Profile.FirstName, Profile.LastNameMike Lasseter
-
Just so you know the following is no different than the query you wrote. In fact, using a group by in some instances is faster than using a distinct (atleast I read that somewhere about Oracle, although it could have changed with a new release of the database).
SELECT Profile.FirstName, Profile.LastName
FROM AssignedTo
INNER JOIN Profile ON AssignedTo.Username = Profile.Username
CROSS JOIN Milestone
WHERE (Milestone.MilestoneID = @MilestoneID)
AND (AssignedTo.ProjectID = @ProjectID)
GROUP BY Profile.FirstName, Profile.LastNameMike Lasseter
Ya, I'm not sure about Oracle but as far as I know with Microsoft SQL there's no performance difference as the execution plan is normally identical. It's purely personal syntax preference. I've always found DISTINCT easier to read than a heap of groups.
Dad always thought laughter was the best medicine, which I guess is why several of us died of tuberculosis. I can picture in my mind a world without war, a world without hate. And I can picture us attacking that world, because they'd never expect it.