Help need for Select Query Remove Empty Cells

Question

Help need for Select Query Remove Empty Cells

tamil.selvanmca

Ten Centuries

Points: 1367
More actions
June 21, 2014 at 12:51 am

#292663

SELECT DISTINCT S.EnrollNo
,S.Name
,ET.Descriptions AS EventName
,SA.Name AS AttendStudent
,'' AS AttendFaculty
FROM StudentEvent SE
INNER JOIN SStudent S ON SE.PresentatorID = S.StudentID
INNER JOIN StudentEventType ET ON SE.EventTypeID = ET.StudentEventTypeID
INNER JOIN StudentEventStudentAttend EA ON SE.StudentEventID = EA.StudentEventID
INNER JOIN SStudent SA ON EA.AttendStudentID = SA.StudentID
WHERE SE.IsActive = 1 AND S.EnrollNo = 'PH1201' AND SE.StudentEventID = 2
UNION ALL
SELECT DISTINCT S.EnrollNo
,S.Name
,ET.Descriptions AS EventName
,'' AS AttendStudent
,AE.FName AS AttendFaculty
FROM StudentEvent SE
INNER JOIN SStudent S ON SE.PresentatorID = S.StudentID
INNER JOIN StudentEventType ET ON SE.EventTypeID = ET.StudentEventTypeID
INNER JOIN StudentEventFacultyAttend FA ON SE.StudentEventID = FA.StudentEventID
INNER JOIN HEmployee AE ON FA.AttendFacultyID = AE.EmployeeID
WHERE SE.IsActive = 1 AND S.EnrollNo = 'PH1201'AND SE.StudentEventID = 2
ORDER BY EnrollNo
Out put
EnrollNo NameEventName AttendStudentAttendFaculty
PH1201ASHKARALIPresentationKUMARA
PH1201ASHKARALIPresentationNILESHRAJ. G
PH1201ASHKARALIPresentationSEENIVASAN
PH1201ASHKARALIPresentation----------- Sawadkar
PH1201ASHKARALIPresentation----------- Naveen
PH1201ASHKARALIPresentation----------- Nitya
PH1201ASHKARALIPresentation----------- Ramesh
PH1201ASHKARALIPresentation----------- Sakthi
Expected OutPut
EnrollNo NameEventName AttendStudentAttendFaculty
PH1201ASHKARALIPresentationKUMARA Sawadkar
PH1201ASHKARALIPresentationNILESHRAJ. GNaveen
PH1201ASHKARALIPresentationSEENIVASAN Nitya
PH1201ASHKARALIPresentation-----------Ramesh
PH1201ASHKARALIPresentation-----------Sakthi

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003921 More actions · Answer 1

Explain why the two with blanks in the expected output are allowed but the first 3 of 5 with the blanks in the original results are not.

Also (and there's no way that I can help without knowing the table structure and content), you would appear to be using DISTINCT because you end up with a Many-to-Many join. Even though it may be returning mostly just the rows you want, the joined rows are still created in the background and actually are a performance issue. Consider using "Divide'n'Conquer" methods for this query rather than trying to do it all in a single query.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 2

Without sample data this is just a shot in the dark.

SELECT DISTINCT S.EnrollNo

,S.Name

,ET.Descriptions AS EventName

,T1.AttendStudent AS AttendStudent

,T2.AttendFaculty AS AttendFaculty

FROM StudentEvent SE

INNER JOIN SStudent S ON SE.PresentatorID = S.StudentID

INNER JOIN StudentEventType ET ON SE.EventTypeID = ET.StudentEventTypeID

CROSS APPLY(SELECT TOP 1 SA.Name AS AttendStudent FROM StudentEventStudentAttend EA ON SE.StudentEventID = EA.StudentEventID INNER JOIN SStudent SA ON EA.AttendStudentID = SA.StudentID)T1

CROSS APPLY(SELECT TOP 1 AE.FName AS AttendFaculty FROM StudentEventFacultyAttend FA ON SE.StudentEventID = FA.StudentEventID INNER JOIN HEmployee AE ON FA.AttendFacultyID = AE.EmployeeID)T2

WHERE SE.IsActive = 1 AND S.EnrollNo = 'PH1201' AND SE.StudentEventID = 2

ORDER BY EnrollNo

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server