Left Join not providing the output as expected

Question

Post reply

Left Join not providing the output as expected

karthik82.vk

SSCrazy

Points: 2002
More actions
December 2, 2015 at 9:12 am

#399414

Hi,
I have two tables tbl_data and tbl_events. The tbl_events tables has a list of eventnames that happens in the each site
The tbl_data has siteno, siteid, eventname, date of event and days difference.
I need to show the events that the sites participated and not participated.
I wrote a sql query using left join with the events table however i'm getting only the rows of eventnames that exists in the tbl_data table.
Here is my sql code
( SELECT tat.[SiteNo]
,tat.[SiteID]
,tat.[EventDate] AS dtEvent
,FPFVStatus
,DATEDIFF(DAY, Grp.MinDate, tat.[EventDate]) AS Date_Diff
,reg.EventName
FROM [dbo].[tbl_data] AS tat
LEFT JOIN [dbo].[tbl_events] reg ON tat.EventName = reg.EventName
LEFT OUTER JOIN ( SELECT [SiteID]
,MIN(EventDate) AS MinDate
FROM [dbo].[tbl_data]
WHERE RNUM = 1
GROUP BY [SiteID]
) AS Grp ON tat.[SiteID] = Grp.[SiteID]
WHERE tat.SiteID = 104
);
My expected output should be
I have attached the data for reference. Any help is highly appreciated.

Viewing 7 posts - 1 through 6 (of 6 total)

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

What happens to the output if you remove the datediff and just show the min date instead?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Shouldn't you be left-joining data to events?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

ChrisM@Work (12/2/2015)
Shouldn't you be left-joining data to events?

Oh, duh! The event's table is the one that has ALL the rows that are needed. I think Chris has it.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

karthik82.vk SSCrazy Points: 2002 More actions · Answer 4

Hi Chris,

I am left joining the events table to the data table. The Left Outer Join is for getting the minimum event date so that i can get the days difference for each events from the first event date.

I tried to take my events table outside the complete query i provided here like below.

SELECT src.SiteNo

,src.SiteID

,reg.EventName

,src.dtEvent

,src.FPFVStatus

,src.Date_Diff

FROM [dbo].[tbl_events] reg

LEFT JOIN ( SELECT tat.[SiteNo]

,tat.[SiteID]

,tat.[EventDate] AS dtEvent

,FPFVStatus

,DATEDIFF(DAY, Grp.MinDate, tat.[EventDate]) AS Date_Diff

,tat.EventName

FROM [dbo].[tbl_data] AS tat

LEFT OUTER JOIN ( SELECT [SiteID]

,MIN(EventDate) AS MinDate

FROM [dbo].[tbl_data]

WHERE RNUM = 1

GROUP BY [SiteID]

) AS Grp ON tat.[SiteID] = Grp.[SiteID]

WHERE tat.SiteID = 104

) AS src ON src.EventName = reg.EventName

When i run this query... i am getting all the missing rows from the events table.

However I am looking for ways to avoid my multiple subqueries...

SoHelpMeCodd SSCertifiable Points: 5871 More actions · Answer 5

Old article, but INF: How to Restrict the Inner Table of an ANSI Outer Join may be relevant

karthik82.vk SSCrazy Points: 2002 More actions · Answer 6

Hi,

Got the Question solved from stackoverflow.

select * from (

select d.siteid, e.EventName

from (select e.siteid from tbl_data e group by e.SiteID) d

cross join lu_events e

) tab

left outer join tbl_data d

on d.EventName=tab.EventName

and d.SiteID=tab.SiteID

order by tab.SiteID