Left Outer Join Not Working?

by

You should move the constraints on prescriptions.filldate into the ON condition of the join, and remove it from the where clause:

LEFT OUTER JOIN prescriptions ON prescriber.dea_no = prescriptions.dea_no
                             AND prescriptions.filldate >= '09-01-12'
                             AND prescriptions.filldate <= '09-17-12'

Otherwise, entries for which there are no prescriptions end up with nulls in prescriptions.filldate, and the WHERE clause throws them away.

More Related Contents:

  1. Explicit vs implicit SQL joins
  2. SQL JOIN and different types of JOINs
  3. SQL update from one Table to another based on a ID match
  4. Does the join order matter in SQL?
  5. SQL Inner-join with 3 tables?
  6. What is the difference between JOIN and UNION?
  7. SQL left join vs multiple tables on FROM line?
  8. SQL Joins Vs SQL Subqueries (Performance)?
  9. SQLite – How do you join tables from different databases?
  10. What’s the best way to join on the same table twice?
  11. Can we use join for two different database tables?
  12. Call a set-returning function with an array argument multiple times
  13. In SQL / MySQL, what is the difference between “ON” and “WHERE” in a join statement?
  14. MySQL – Selecting data from multiple tables all with same structure but different data
  15. Sql PIVOT and string concatenation aggregate
  16. Explain JOIN vs. LEFT JOIN and WHERE condition performance suggestion in more detail
  17. ORA – 00933 confusion with inner join and “as”
  18. MySQL: “The SELECT would examine more than MAX_JOIN_SIZE rows”
  19. Venn Diagram for Natural Join
  20. SQL query: Simulating an “AND” over several rows instead of sub-querying
  21. Why do multiple-table joins produce duplicate rows?
  22. SQL SELECT from multiple tables
  23. How do I format date and time on ssrs report?
  24. IN vs. JOIN with large rowsets
  25. SQL join on multiple columns in same tables
  26. Left Join without duplicate rows from left table
  27. Is NATURAL (JOIN) considered harmful in production environment?
  28. SQL select join: is it possible to prefix all columns as ‘prefix.*’?
  29. Removing duplicate rows (based on values from multiple columns) from SQL table
  30. sql query joins multiple tables – too slow (8 tables)

Leave a Comment