Need help with join
I have a query that isn't working the way I think it should:
If no department is assigned to an employee, there will be no record in the EmpDepts table for that employee record id.
That top query that isn't working should be returning 6 records out of my 200 records because 6 are not assigned to any departments, but my query is returning only 2 records, thus, missing other 4 who have not been assigned to any departments.
I've checked the record structure of the 2 records that are being 'caught' by the top query vs the 4 that are not being caught and I can't see any differences. I think the main thing about that query is that I've manually verified that they are not in the EmpDepts table...so I'm puzzled as to why the query doesn't catch all 6 records.
Thanks for any help.
this is what you want:
*** CAUTION ***
That query works in this particular instance, but it will fail in most other situations.
When you use a condition in the WHERE clause that refers to the *dependent* table in an outer join (that is, the RIGHT table in a LEFT JOIN, for example), you just converted that outer join into an INNER join!!!
The sole exception to this rule is when the condition you are testing is IS NULL (or IS NOT NULL) as Bubikol is doing here.
For other conditions, you *MUST* move the condition on the dependent table to the ON section of the query, thus (just an example, not applicable to this case):
Again, not relevant in this case, but a fair warning for the future.
|All times are GMT +1. The time now is 02:42 PM.|
Powered by vBulletin®
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.