SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query to return the rows with values


Query to return the rows with values

Author
Message
Durai Samuel
Durai Samuel
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 65
--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...?
Andy Hyslop
Andy Hyslop
SSC Eights!
SSC Eights! (977 reputation)SSC Eights! (977 reputation)SSC Eights! (977 reputation)SSC Eights! (977 reputation)SSC Eights! (977 reputation)SSC Eights! (977 reputation)SSC Eights! (977 reputation)SSC Eights! (977 reputation)

Group: General Forum Members
Points: 977 Visits: 3037
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search