Re: SQL Query - Help with Joins

Just for awareness, to make the WHERE clause function for some future query that might require it, something like *either* of the following replacement for the ANDed tests [or similar logic] would be required in the WHERE clause:
IFNULL(budget.month, 'june') = 'june'
AND IFNULL(budget.year , 2007 ) = 2007
( budget.month = 'june' OR budget.month IS NULL )
AND ( budget.year = 2007 OR budget.year IS NULL )
The selection in the first query below, prevents the NULL generated rows from being included; the row will be generated by the join, but will be excluded by the WHERE selection because a comparison test is _not true_ for the NULL value. When the selection is applied in the join selection as in the second query below, that same exclusion does not occur because the row is still generated by the join [but there is no selection in the WHERE clause to exclude].

Regards, Chuck
-- All comments provided "as is" with no warranties of any kind whatsoever and may not represent positions, strategies, nor views of my employer

travo wrote:
Ah, I nailed it. Rather than;

LEFT OUTER JOIN
budget ON
budget.sales guy = sales.sales guy
AND
budget.product group = SUBSTRING(sales.product group,1,3)
WHERE
1 = 1
AND budget.month = june
AND budget.year = 2007

I needed to include those clauses in the join;

LEFT OUTER JOIN
budget ON
budget.sales guy = sales.sales guy
AND budget.product group = SUBSTRING(sales.product group,1,3)
AND budget.month = june
AND budget.year = 2007

My nulls have appeared. Sweet!

Chuck, thanks!

Travis
.

Relevant Pages

  • Re: VB-ADO-SQL Server : SQL Server performs logins after some queries
    ... If you have some joins or WHERE clause in your statement, ... Also try to minimize selection of the records using WHERE ... Incase of actual action query, ... >> of queries and I've concluded that in case of an internet conection the ...
    (microsoft.public.vb.database.ado)
  • Re: Parameters
    ... I basically have four options in the combo box, can I add an All to that selection or maybe a check box? ... RowSourceType you can add "ALL" to the query by making it a Union query: ... ComboBox drop-down list. ... In the query that is based on the ComboBox the WHERE clause will look ...
    (microsoft.public.access.queries)
  • Re: How? Linking a select all box to a query field criteria line
    ... >> 'Define sWhere Clause Here ... >> am linking the combo box range to the criteria line of my query. ... >> will pick up the selection range from the combo boxes if it doesn't have ...
    (microsoft.public.access.queries)
  • Re: Specification of multiple link criteria
    ... based on the same query. ... Main form "Maintenace CSS" and subform "Selection ... How do I set the value of the link field to the value ... SQL View to see the WHERE clause. ...
    (microsoft.public.access.reports)
  • RE: Any good T-SQL quick reference recommended?
    ... The full syntax of the SELECT ... SELECT Clause ... Specifies the columns to be returned by the query. ... Specifies that duplicate rows can appear in the result set. ...
    (microsoft.public.sqlserver.programming)