Query to return the rows with values

  • --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...?

  • 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