Hi All

I dont know why on windows 2000 a simple left outer join in a query get all
records but not ther firs one if the key of the child is null.

This not happend on windows 98

Any help will be appreciate

Thank in advanced
Daniel

The database would be....?

news:3c71430e$1_1@dnews...

What database are you using? What is the SQL statement? You have to give us
a few basic clues. :)

--
Bill
(TeamB cannot answer questions received via email)

I am using Paradox with Delphi 6 and BDE version that came with Delphi 6

The query is very simple:

Select m.keyfield, m.field2....., d.Field2
from Master m left outer join Detail d
on m.keyfield=d.keyfield

If the first one record of the Detail table is null (d.keyfield ) then I get
all records but not the first one even the others records have null the key
field too.

It is very weird behaivor because this not happen on window 98 with the same
enviroment (D6), only on window 2000

If I fill the first key field with sonmethin different to null, it is work
fine.

Thank in advanced
daniel
"Bill Todd (TeamB)" <bill_nos...@dbginc.com> escribi en el mensaje
news:3c717dfa_1@dnews...

Are you saying that the first detail record exists but that its primary key
field is null or are you saying that there is no detail record for the first
master record? Perhaps an example would help clarify what you are seeing.

--
Bill
(TeamB cannot answer questions received via email)

Just a guess:

I *think* you're expecting to be able to join using "NULL" as key "value",
like you do with other normal key values.  Well you can't -- Null is treated
as an "undefined" special case.   This is also why you cant do e.g. "where
Field = Null" (will always return false) and have to do "where Field is
Null".  I suspect you should do something like the following with your query
to get the expected results;

 Select m.keyfield, m.field2....., d.Field2
 from Master m left outer join Detail d
 on (m.keyfield=d.keyfield) or (m.keyfield is null and d.keyfield is null)

I just knocked up a very quick demo to illustrate the difference, and will
post to attachments in a moment.  Look for "NullJoinDemo.zip" if interested.

HTH

Walter

Example:

Master Table

KeyField, field1, field2........KeyFieldChild
1              12        A            null
2              13        B            null

Detail Table

KeyFieldChild, fieldchild1, fieldchild2...
1                        d1            d2
2                        d3            d4
.
.
if I join this two table with a left outer ON WINDOWS 2000. I get

Select KeyField, field1, field2........KeyFieldChild  from
MasterTable m left outer join DetaitTable d on
m.KeyFieldChild=d..KeyFieldChild

2              13        B            null

Note that there are not child recods for this join but I would get all my
Master Records and not to loss anything.

Than in Advanced
Daniel

"Bill Todd (TeamB)" <bill_nos...@dbginc.com> escribi en el mensaje
news:3c72b40e_1@dnews...

You seem to have the master and detail tables reversed. In any case, you
cannot perform a join when one of the fields is null. Null is not a value,
it is a state that means the value is undefine. Null is never equal to
anything. Even the equality Null = Null is false.

--
Bill
(TeamB cannot answer questions received via email)

"Bill Todd (TeamB)" <bill_nos...@dbginc.com> wrote in message
news:3c74157f_1@dnews...

He did do a left join, so in this case he should get his master records.
Definitely odd that the second master would appear but not the first, and
only on some machines.

The only thing I can think of, since these are Paradox tables, is corrupt
indexes on those machines, so Daniel should try rebuilding the indexes and
then test again.

--
Wayne Niddery (Logic Fundamentals, Inc.)
RADBooks: http://www.logicfundamentals.com/RADBooks/delphibooks.html
In a tornado, even turkeys can fly. - unknown

I stand corrected. Thanks.

--
Bill
(TeamB cannot answer questions received via email)