October 16, 2012 at 11:55 pm
--Sample script
CREATE TABLE Professor
(
ProfID INT
, DeptID INT
, ProfName VARCHAR(100)
, CourseID INT
, LanguageID INT
)
INSERT INTO Professor
VALUES(1, 201, 'Maria Soosai', 22, 16)
INSERT INTO Professor
VALUES(2, 202, 'Herbert Santhappa', 23, 17)
CREATE TABLE Class
(
DeptID INT
, CourseID INT
, LanguageID INT
, NumberOfClass INT
)
INSERT INTO Class
VALUES(201, 22, NULL, 1)
INSERT INTO Class
VALUES(201, NULL, NULL, 1)
INSERT INTO Class
VALUES(202, 22, NULL, 1)
INSERT INTO Class
VALUES(202, NULL, 17, 1)
-- Sample script ends here
-- This is my Query
SELECT P.ProfID, P.ProfName, C.CourseID, C.LanguageID, C.NumberOfClass
FROM Professor P INNER JOIN Class C ON C.DeptID = P.DeptID
I Executed the above Query and i'm getting the result as
-----------------------------------------------------------------
ProfIDProfNameCourseIDLanguageIDNumberOfClass
-----------------------------------------------------------------
1Maria Soosai22NULL1
1Maria SoosaiNULLNULL1
2Herbert Santhappa22NULL1
2Herbert SanthappaNULL171
------------------------------------------------------------------
I want the result like the below (Expected result)
-----------------------------------------------------------------
ProfIDProfNameCourseIDLanguageIDNumberOfClass
-----------------------------------------------------------------
1Maria Soosai22NULL1
2Herbert SanthappaNULL171
------------------------------------------------------------------
Filter has to be applied like
If the CourseID has value then i need to select only the row where the CourseID has value
else
i have to select null rows as well
if LanguageID is not null then i have to select only the languageID otherwise i can select the NULL rows as well
If CourseID is null and LanguageID has value then i have to select the row which has the languageID
Can any one please get me the Query with applying the filter based on this...?
October 17, 2012 at 2:36 am
Hi
Does this help?
SELECT *
FROM
(
SELECT
P.ProfID
,P.ProfName
,CASE WHEN RowNum1.CourseID IS NOT NULL THEN RowNum1.CourseID ELSE RowNum2.CourseID END AS CourseID
,CASE WHEN RowNum2.LanguageID IS NOT NULL THEN RowNum2.LanguageID ELSE RowNum1.LanguageID END AS LanguageID
,RowNum1.RN1
,RowNum2.RN2
FROM
Professor P
LEFT JOIN(SELECT
DeptID AS DeptID
, LanguageID AS LanguageID
, CourseID AS CourseID
, ROW_NUMBER() OVER (PARTITION BY DeptID ORDER BY CourseID DESC ) RN1
FROM
Class
) AS RowNum1
ON P.DeptID = RowNum1.DeptID
LEFT JOIN(SELECT
DeptID AS DeptID
, LanguageID AS LanguageID
, CourseID AS CourseID
, ROW_NUMBER() OVER (PARTITION BY DeptID ORDER BY LanguageID DESC ) RN2
FROM
Class
) AS RowNum2
ON P.DeptID = RowNum2.DeptID
) Tbl
WHERE
Tbl.RN1 = 1
AND Tbl.RN2 = 1
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply