CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2005
momoterraw
momoterraw

Reputation: 164

SQL Statement doesnt work after putting a where clause

SELECT     
   Event.Id, Event.Title, Event.StartDateTime, Event.DeadlineDate, 
   [File].ShortFileName, People.Initials
FROM         
   People 
INNER JOIN
   People2Event ON People.Id = People2Event.PeopleId 
RIGHT OUTER JOIN
   [File] 
INNER JOIN
   File2Event ON [File].Id = File2Event.FileId 
RIGHT OUTER JOIN
   Event ON File2Event.EventId = Event.Id ON People2Event.EventId = Event.Id

I have this statement it works print all record. Below doesn't work it's the same statement with a where function. Does anyone know how to fix?

SELECT     
   Event.Id, Event.Title, Event.StartDateTime, Event.DeadlineDate, 
   [File].ShortFileName, People.Initials
FROM         
   People 
INNER JOIN
   People2Event ON People.Id = People2Event.PeopleId 
RIGHT OUTER JOIN
   [File] 
INNER JOIN
   File2Event ON [File].Id = File2Event.FileId 
RIGHT OUTER JOIN
   Event ON File2Event.EventId = Event.Id ON People2Event.EventId = Event.Id
WHERE 
   Event.ID = 8888

Upvotes: 1

Views: 276

Answers (3)

Michael Fredrickson
Michael Fredrickson

Reputation: 37398

EDIT:

As AndriyM points out, since the condition that was in the WHERE clause was on the right side of the RIGHT OUTER JOIN, moving to the ON would have no effect. The OP posted a query that contained only INNER JOIN that he says solved the problem, so there must have been some other issue with his query.

If you add a filter in your WHERE clause for a table that is part of an OUTER JOIN, it will eliminate any NULL rows, effectively turning your OUTER JOIN to an INNER JOIN.

Instead of placing the condition in your WHERE clause, move it to your ON:

File2Event ON [File].Id = File2Event.FileId RIGHT OUTER JOIN
Event 
    ON File2Event.EventId = Event.Id 
    AND People2Event.EventId = Event.Id
    AND Event.ID = 8888

Also, a tip on style, I'd avoid using RIGHT JOIN, and instead re-write your query to use LEFT JOIN... this typically makes the logic easier to follow.

Upvotes: 7

TimothyAWiseman
TimothyAWiseman

Reputation: 14873

This would properly return no rows if there is no Event.Id.

One possible source of confusion may be that you are using a "right outer join" when you bring in Event so you might possibly expect it to still show the rows from the other table even with the filter.

But that filter is in your where clause, so the query as a whole will only return rows that match the condition of the filter. In this case meaning that you will only see rows where event.id is 8888 and nothing else.

If you want it to show the rows from the event table but only show the information from the other tables when the event.id is 8888 then you can put that as part of your join. It is a bit unusual to use a constant instead of a value from another table as part of a join clause, but the Server will happily accept it and only join when that condition is meant. When using inner joins this is hardly ever useful, but for outer joins it can help control when data from another table is brought in. It would look like:

SELECT     Event.Id, Event.Title, Event.StartDateTime, Event.DeadlineDate, [File].ShortFileName, People.Initials
FROM         People INNER JOIN
                      People2Event ON People.Id = People2Event.PeopleId RIGHT OUTER JOIN
                      [File] INNER JOIN
                      File2Event ON [File].Id = File2Event.FileId RIGHT OUTER JOIN
                      Event ON File2Event.EventId = Event.Id 
                      ON People2Event.EventId = Event.Id
                          AND Event.ID = 8888

Upvotes: 1

momoterraw
momoterraw

Reputation: 164

SELECT     Event.Id, Event.Title, Event.StartDateTime, Event.DeadlineDate, [File].ShortFileName, People.Initials
FROM         [File] INNER JOIN
                      File2Event ON [File].Id = File2Event.FileId INNER JOIN
                      People INNER JOIN
                      People2Event ON People.Id = People2Event.PeopleId INNER JOIN
                      Event ON People2Event.EventId = Event.Id ON File2Event.EventId = Event.Id
WHERE     (Event.Id = 8888)

This does the trick for some reason. Used query designer.

Upvotes: 0

Related Questions