March 4, 2013 at 1:15 pm
In Table A I have a list of projects (tbprojects).
In Table B I have a list of status updates for projects with a month and year columns (tbstatus).
I want to create a query that will return all projects in the list and then show all status updates for a specific month and year.
If aproject has no status update for that given month, i still want it to show up in the query result, but with simply "NULL" values for the fields from the status update table.
How would I write a query like that?
I tried this:
select * from tbprojects left join tbstatus on tbprojects.id = tbstatus.projectid where month = 2 and year = 2013
But this only returns the projects from tbprojects that have a status update for the given month and year. I need all of the projects to appear in the result. How can i modify my query to do that?
March 4, 2013 at 1:19 pm
Move it into the ON clause so it only affects the left join population instead of the result population.
When dealing with outer joins, think of it this way:
Criteria in the ON component happen before linking the data to the main dataset.
Criteria in the WHERE component happen after the result is built and then removes from the results.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply