January 6, 2010 at 6:54 pm
Hi,
I have a stored procedure which is giving the details of the courses and organisation from two tables with inner join. But it is giving the distinct data according to the course. but the organisation for those couses are will repeat anyway.
The aspx code for the search page, is using the same stored procedure to display the search result and the details of the courses.
What it is doing, it will select a country and the search button will generate data grid with organisation name in that country, in which the organisation name is a hyper link, if we click that link, it will give more details about organisation and courses.
but the datagrid is giving say 6 times orgname for 6 courses and it is correct, because the dataset is grouped by courses.
But I need to use the same stored procedure, todisplay the first data grid with the hyperlink for the oraganisations only once and when clicked the hyperlink , give the details of the courses.Now all the 6 hyperlink is giving all details that we needed, but giving it six times.
i tried to make the distinct value in the data grid,but not working.
so i thought to group it by orgname and etc..
I am looking for the stored procedure to group by orgname and giving all details of its courses in stored procedure.
attached is the stored proc that currenlty using, very simple one.
Do I need to use, CTE??
please help
ALTER PROCEDURE [dbo].[CoursesSearchResults]
(
@GeneralType nvarchar(255),
@OrgNameSearch nvarchar(255) = NULL,
@Country nvarchar(255) = NULL
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON
SELECT distinct
o.orgname,
o.orgid,
o.Region,
o.Country,
o.State,
o.Email,
o.Website,
o.GeneralType,
o.OrgType,
c.CourseID,
c.CourseType,
CASE MinAc
WHEN 0.0 THEN ''
WHEN 10 THEN ''
ELSE CAST(MinAcc as varchar(10))
END as MinAcc,
CASE MinGen
WHEN 0.0 THEN ''
WHEN 10 THEN ''
ELSE CAST(MinGen as varchar(10))
END as MinGen,
c.AcRos,
c.Info
FROM Organisations o INNER JOIN dbo.Courses c
ON o.OrgId = c.OrgId
WHERE o.GeneralType LIKE @GeneralType
AND o.Country LIKE @Country
AND o.OrgName LIKE @OrgNameSearch
END
January 6, 2010 at 11:27 pm
The devil's in the data. Please read and heed the following...
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
--Jeff Moden
January 7, 2010 at 7:36 am
There are some potential ways to "hack" this in the SQL, but this is really a display issue and should thus be handled in the .NET code. It's been awhile since I used a datagrid, but there must be a way to ignore duplicates. If not I know you could code it in the code-behind.
Here's one way to do it in SQL (uses AdventureWorks), but it is more expensive than just returning the duplicate data:
SELECT
CASE WHEN ROW_NUMBER() OVER (PARTITION BY D.DepartmentID ORDER BY D.DepartmentID) > 1 THEN NULL
ELSE D.[Name]
END AS department,
A.EmployeeID AS employee
FROM
HumanResources.Department AS D JOIN
HumanResources.EmployeeDepartmentHistory AS EDH
ON D.DepartmentID = EDH.DepartmentID JOIN
HumanResources.Employee AS A
ON EDH.EmployeeID = A.EmployeeID
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply