Click here to monitor SSC
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
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 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
Say Hey Kid
Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)

Group: General Forum Members
Points: 706 Visits: 3022
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