Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query to return the rows with values Expand / Collapse
Author
Message
Posted Tuesday, October 16, 2012 11:55 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 01, 2013 12:41 PM
Points: 17, Visits: 61
--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
-----------------------------------------------------------------
ProfID ProfName CourseID LanguageID NumberOfClass
-----------------------------------------------------------------
1 Maria Soosai 22 NULL 1
1 Maria Soosai NULL NULL 1
2 Herbert Santhappa 22 NULL 1
2 Herbert Santhappa NULL 17 1
------------------------------------------------------------------

I want the result like the below (Expected result)

-----------------------------------------------------------------
ProfID ProfName CourseID LanguageID NumberOfClass
-----------------------------------------------------------------
1 Maria Soosai 22 NULL 1
2 Herbert Santhappa NULL 17 1
------------------------------------------------------------------

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...?
Post #1373634
Posted Wednesday, October 17, 2012 2:36 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 3:28 AM
Points: 672, Visits: 2,624
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
Post #1373676
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse