Query to return the rows with values

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Development » Query to return the rows with values

Query to return the rows with values

Rate Topic

Display Mode

Topic Options

Author

Message

Durai Samuel

Posted Tuesday, October 16, 2012 11:55 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 6:11 AM
Points: 12, Visits: 52

--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

Andy Hyslop

Posted Wednesday, October 17, 2012 2:36 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 1:50 AM
Points: 595, Visits: 2,138

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 »

Permissions