Re: Inner join on table with NULL's

JKarchner wrote:
"Carl Colijn" wrote:
Hi group,

I want to perform an inner join between two tables.

The first (named 'Data') has some data columns and two classification
columns ('Period' = Date/Time, and 'SequenceNr' = Long) that
identify the type of record. If the record is actual, then the
classification columns contains NULL's. If the record is
historical, then the record contains the correct date for the period
and a sequence number (there can be more than one set of historical
records per period).

The second table (named 'Conf_History') is used as a configuration
table to determine the period and sequence nr. to show throughout
the database's forms. It thus contains the same two columns
'Period' and 'SequenceNr'. It holds exactly one record.

Now comes the tricky part. I want to base my forms on a RWOP query
to access the data in the backend database, but they should only
show the correct set of records (according to the 'Conf_History'
table). My first try was to use an inner join in the query:
SELECT Data.*
INNER JOIN Conf_History
ON Data.Period = Conf_History.Period
AND Data.SequenceNr = Conf_History.SequenceNr
but this didn't work because NULL = NULL doesn't work. The next
attempt was:
SELECT Data.*
FROM Data, Conf_History
WHERE Data.Period = Conf_History.Period
AND Data.SequenceNr = Conf_Historiy.SequenceNr
OR (Data.Period IS NULL AND Conf_Historiy.Period IS NULL)
but this seems inefficient (making a cross join first combined with
a more convoluted WHERE clause). And then the result set of these
queries is not appendable nor modifyable (a requirement for my
forms).

The alternative I came up with is to use a custom VBA function to
check each record:
SELECT *
FROM Data
WHERE IsCorrectPeriod(Period, SequenceNr)
in combination with the VBA routine:
Private g_dPeriod As Date ' These get initialized on
DB startup
Private g_nSequenceNr As Long ' These get initialized on DB
startup Private g_bIsCurrent As Boolean ' These get initialized
on DB startup Public Function IsCorrectPeriod(dPeriod As
Variant, nSequenceNr As
Variant)
If IsNull(dPeriod) And g_bIsCurrent Then
IsCorrectPeriod = True
Else
IsCorrectPeriod = (dPeriod = g_dPeriod) And _
(g_nSequenceNr =
nSequenceNr) End If
End Function
but I can imagine this gives even more overhead... The resultset of
the query is appendable and modifyable however, exacty as I wanted.

And now for the $1.000.000 question: is there an alternative that
given me better performance and/or less complicated queries?

Thanks in advance,
Carl Colijn
I dont know if this might help at all but i had to do something
similar to that, and i used LEFT join and used for criteria: NULL.
for me this returned all the results with NULL in that column, and
none of the results with anything in that column. like i said this
worked for me and i am not quite sure if it will work for you....

Hi JKarchner,

Unfortunately this solution doesn't work in my situation. In my case I do
not always want to select the records with a NULL in them, but conditionally
select either the NULL-containing records _or_ the records containing a
certain value. The condition is stored in another record in another table,
which holds the correct value to check for (NULL or a real value).
Therefore my first thought was to use a JOIN to select the records with the
right value in them, either NULL or that certain value.

The problem is that Access (and I believe all SQL implementations) cannot
deal with NULL values at all when using a JOIN, so that "table1.column =
table2.column" cannot be used as a join condition if they both contain NULL
(NULL <> NULL in SQL, by design I believe).

Anyway, thanks for the suggestion,
Kind regards,
Carl Colijn


.

Relevant Pages

  • Inner join on table with NULLs
    ... I want to perform an inner join between two tables. ... The first has some data columns and two classification ... determine the period and sequence nr. ... Private g_nSequenceNr As Long ' These get initialized on DB startup ...
    (microsoft.public.access.queries)
  • RE: Inner join on table with NULLs
    ... I want to perform an inner join between two tables. ... determine the period and sequence nr. ... I want to base my forms on a RWOP query to ... Private g_nSequenceNr As Long ' These get initialized on DB startup ...
    (microsoft.public.access.queries)
  • Re: find value from a query
    ... Use [Max Classification] in the header. ... cboClassification are secret, confidential, FOUO and unclass. ... FROM tblStatusChoices INNER JOIN (tblPersonnel INNER JOIN (tblNumbered ...
    (microsoft.public.access.reports)
  • Re: Report/Query Filtering
    ... numbers that the only difference is the classification. ... FROM Categories AS Categories_1 INNER JOIN (Products AS Products_1 INNER JOIN ... CategoryID) INNER JOIN ((Customers INNER JOIN Orders ON Customers.CustomerID ... Then a query of the table becomes a bit easier. ...
    (microsoft.public.access.reports)
  • Re: find value from a query
    ... Use [Max Classification] in the header. ... cboClassification are secret, confidential, FOUO and unclass. ... FROM tblStatusChoices INNER JOIN (tblPersonnel INNER JOIN (tblNumbered ...
    (microsoft.public.access.reports)