Left Join Not Properly

[Guest]

I'm joining two tables using left join and noticed that some (but not all) of
the rows from the left table were being excluded from the result set when the
join condition is not satisfied. The following SQL returns one row, as it
should:
SELECT Rbt.*, FullDWGPP.*
FROM Rbt LEFT JOIN FullDWGPP ON
Rbt.BILL_MO >= FullDWGPP.ROW_BEG_DT
AND Rbt.BILL_MO >= FullDWGPP.CSPI_EFF_DT
AND Rbt.BILL_MO <= FullDWGPP.ROW_END_DT
AND Rbt.BILL_MO <= FullDWGPP.CSPI_TERM_DT
AND Rbt.GROUP = FullDWGPP.GRGR_ID
AND Rbt.SUB_GROUP = FullDWGPP.SGSG_ID
AND Rbt.PLAN_CD = MID(FullDWGPP.PDPD_ID,4,4)
WHERE Rbt.GROUP = '00500530';

If I remove the WHERE clause, the same SQL returns many rows, but the row
for group '00500530' is not amongst them. Tried all manner of parenthesis but
I always get the same results - some but not all of the rows that don't
statisfy the join condition are omitted. And since I'm using the MID function
and >= conditions, I'm not able to use the query builder.

Any help would be greatly appreciated.

 

[Guest]

You appear to be comparing dates using a >= and <= and that may not work
depending on the format and definition of the date columns.

-Dorian

 

[Guest]

A worthy thought, but ...

Because of some other limitations, all dates are defined as 10-character
text fields in DB2's date format (YYYY_MM_DD). IOW, they're string variables,
not date/time variables.

This works for the 50,000+ rows where the join condition is satisfied. And
it works for all but some of the cases where the join condition is not
satisfied. And it works when I add the WHERE clause for any of the groups
that are being omitted from the result set.

 

[privatenews]

Hello,

It seems string comparison is a little different from date comparison. You
may want to convert the text to date by using CVDate/left/mid/right
functions before comparision.

Also, make sure the string format is consistent for the omitted records.


Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------

 

[Guest]

Hi Peter -

I'm as sure as I can be that the choice of representation for dates is not
the issue. I'm simply comparing some strings that happen to encode some dates
in YYYY-MM-DD format. ANd I know that the rows in question have valid data
because I've looked and because the inclusion of a simple WHERE clause shows
the comparisons to be working as expected.

Please understand that I'm NOT saying that the JOIN condition fails for
certain rows. I'm saying that the JOIN is seemingly not even being ATTEMPTED
for certain rows.

Here's what I expected from the LEFT JOIN:
Rbt Row 1 + [FullDWGPP row x | null]
Rbt Row 2 + [FullDWGPP row y| null]
Rbt Row 3 + [FullDWGPP row z | null]
....

The success or failure of the JOIN conditions (including the date
comparisons) would determine whether the right side of the resulting row is
a) FullDWGPP data or b) null but should have no bearing on the presence or
absence of rows themselves.

Here's what I get, though:
Rbt Row 1 + FullDWGPP row x
Rbt Row 3 + FullDWGPP row z

Rbt Row 2 is missing from the result set entirely. If I add the WHERE
clause, without changing anything else, I get:
Rbt Row 2 + FullDWGPP row y

Pete

 

[Guest]

Posted the previous before my morning coffee...

Please understand that I'm NOT saying that the JOIN condition fails for
certain rows. I'm saying that the JOIN is seemingly not even being ATTEMPTED
for certain rows.

I should have said:
Please understand that I'm NOT saying that the JOIN condition fails
UNEXPECTEDLY
for certain rows. I'm saying that the JOIN is seemingly not even being
ATTEMPTED
for certain rows. (Capital letters used in lieu of italics - I don't mean to
shout...)

Rbt Row 2 is missing from the result set entirely. If I add the WHERE
clause, without changing anything else, I get:
Rbt Row 2 + FullDWGPP row y

I should have said
Rbt Row 2 + null

To summarize (an hopefully clarify):
SELECT A.*, B.*
FROM A LEFT JOIN B
ON a bunch of conditions

should return:
A1 + Bx
A2 + null
A3 + By
A3 + Bz
A4 + null
....

Instead, it's dropping some of rows:
A1 + Bx
A3 + By
A3 + Bz
A4 + null

Meanwhile
SELECT A.*, B.*
FROM A LEFT JOIN B
ON a bunch of conditions
WHERE A.key = 2

returns the correct result:
A2 + null

and
SELECT A.*, B.*
FROM A LEFT JOIN B
ON a bunch of conditions
WHERE A.key in (1, 2, 3, 4)

returns:
A1 + Bx
A2 + null
A3 + By
A3 + Bz
A4 + null

So the question is: Why would A2 + null be in the result set when I include
a WHERE clause but fail to be in the result set when I don't? The WHERE
clause is simply specifying a subset of rows from A (where the subset
includes row A2) but the LEFT JOIN absent the WHERE clause should be using
the entire set (which also includes row A2).

Please note that I don't know what happens under the covers with the WHERE
clause. I don't know if it's filtering A before the JOIN or if it filtering
the JOIN once that's completed. Either way, row A2 + null should be in the
result set with or without the WHERE clause...

Thanks again,
Pete

Hi Peter -

I'm as sure as I can be that the choice of representation for dates is not
the issue. I'm simply comparing some strings that happen to encode some dates
in YYYY-MM-DD format. ANd I know that the rows in question have valid data
because I've looked and because the inclusion of a simple WHERE clause shows
the comparisons to be working as expected.

Please understand that I'm NOT saying that the JOIN condition fails for
certain rows. I'm saying that the JOIN is seemingly not even being ATTEMPTED
for certain rows.

Here's what I expected from the LEFT JOIN:
Rbt Row 1 + [FullDWGPP row x | null]
Rbt Row 2 + [FullDWGPP row y| null]
Rbt Row 3 + [FullDWGPP row z | null]
...

The success or failure of the JOIN conditions (including the date
comparisons) would determine whether the right side of the resulting row is
a) FullDWGPP data or b) null but should have no bearing on the presence or
absence of rows themselves.

Here's what I get, though:
Rbt Row 1 + FullDWGPP row x
Rbt Row 3 + FullDWGPP row z

Rbt Row 2 is missing from the result set entirely. If I add the WHERE
clause, without changing anything else, I get:
Rbt Row 2 + FullDWGPP row y

Pete

Hello,

It seems string comparison is a little different from date comparison. You
may want to convert the text to date by using CVDate/left/mid/right
functions before comparision.

Also, make sure the string format is consistent for the omitted records.


Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[privatenews]

Hello Pete,

Thank you for your clarification. I agree with you the rows shall appear no
matter which join condition is used.

It seems the issue is that when using string comparsion in left join
condition, it omits some rows. Please rest assured that this has been
reported to the proper channel. If there is any update, we will let you
know.

If you'd like to solve it in a effificent manner, I recommend that you open
a Support incident with Microsoft Product Support Services so that a
dedicated Support Professional can assist with this case. If you need any
help in this regard, please let me know.

For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp

If you'd still like to continue working via the newsgroup, I want to set
your expectations that the issue might take a long time to narrow down.
During the course of troubleshooting, we may redirect you to PSS if
required. For now, please try the following and let me know how it goes:

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================


This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------

Thread-Topic: Left Join Not Properly
thread-index: AcZUxCS/1Ut0FYr/SZqHBVdXG/fhUg==
X-WBNR-Posting-Host: 12.108.117.125
From: =?Utf-8?B?UGV0ZQ==?= <[email protected]>
References: <[email protected]>

<[email protected]>
<[email protected]>

Subject: RE: Left Join Not Properly
Date: Fri, 31 Mar 2006 05:08:03 -0800
Lines: 219
Message-ID: <[email protected]>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
Newsgroups: microsoft.public.access.queries
Path: TK2MSFTNGXA01.phx.gbl
Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.access.queries:274546
NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
X-Tomcat-NG: microsoft.public.access.queries

Posted the previous before my morning coffee...

Please understand that I'm NOT saying that the JOIN condition fails for
certain rows. I'm saying that the JOIN is seemingly not even being ATTEMPTED
for certain rows.

I should have said:
Please understand that I'm NOT saying that the JOIN condition fails
UNEXPECTEDLY
for certain rows. I'm saying that the JOIN is seemingly not even being
ATTEMPTED
for certain rows. (Capital letters used in lieu of italics - I don't mean to
shout...)

Rbt Row 2 is missing from the result set entirely. If I add the WHERE
clause, without changing anything else, I get:
Rbt Row 2 + FullDWGPP row y

I should have said
Rbt Row 2 + null

To summarize (an hopefully clarify):
SELECT A.*, B.*
FROM A LEFT JOIN B
ON a bunch of conditions

should return:
A1 + Bx
A2 + null
A3 + By
A3 + Bz
A4 + null
...

Instead, it's dropping some of rows:
A1 + Bx
A3 + By
A3 + Bz
A4 + null

Meanwhile
SELECT A.*, B.*
FROM A LEFT JOIN B
ON a bunch of conditions
WHERE A.key = 2

returns the correct result:
A2 + null

and
SELECT A.*, B.*
FROM A LEFT JOIN B
ON a bunch of conditions
WHERE A.key in (1, 2, 3, 4)

returns:
A1 + Bx
A2 + null
A3 + By
A3 + Bz
A4 + null

So the question is: Why would A2 + null be in the result set when I include
a WHERE clause but fail to be in the result set when I don't? The WHERE
clause is simply specifying a subset of rows from A (where the subset
includes row A2) but the LEFT JOIN absent the WHERE clause should be using
the entire set (which also includes row A2).

Please note that I don't know what happens under the covers with the WHERE
clause. I don't know if it's filtering A before the JOIN or if it filtering
the JOIN once that's completed. Either way, row A2 + null should be in the
result set with or without the WHERE clause...

Thanks again,
Pete

Hi Peter -

I'm as sure as I can be that the choice of representation for dates is not
the issue. I'm simply comparing some strings that happen to encode some dates
in YYYY-MM-DD format. ANd I know that the rows in question have valid data
because I've looked and because the inclusion of a simple WHERE clause shows
the comparisons to be working as expected.

Please understand that I'm NOT saying that the JOIN condition fails for
certain rows. I'm saying that the JOIN is seemingly not even being ATTEMPTED
for certain rows.

Here's what I expected from the LEFT JOIN:
Rbt Row 1 + [FullDWGPP row x | null]
Rbt Row 2 + [FullDWGPP row y| null]
Rbt Row 3 + [FullDWGPP row z | null]
...

The success or failure of the JOIN conditions (including the date
comparisons) would determine whether the right side of the resulting row is
a) FullDWGPP data or b) null but should have no bearing on the presence or
absence of rows themselves.

Here's what I get, though:
Rbt Row 1 + FullDWGPP row x
Rbt Row 3 + FullDWGPP row z

Rbt Row 2 is missing from the result set entirely. If I add the WHERE
clause, without changing anything else, I get:
Rbt Row 2 + FullDWGPP row y

Pete

Hello,

It seems string comparison is a little different from date comparison. You
may want to convert the text to date by using CVDate/left/mid/right
functions before comparision.

Also, make sure the string format is consistent for the omitted records.


Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------
Thread-Topic: Left Join Not Properly
thread-index: AcZUF55aEEUGlOEqRY+dqR1MIL7Nww==
X-WBNR-Posting-Host: 12.108.117.125
From: =?Utf-8?B?UGV0ZQ==?= <[email protected]>
References: <[email protected]>
<[email protected]>
Subject: RE: Left Join Not Properly
Date: Thu, 30 Mar 2006 08:33:04 -0800
Lines: 43
Message-ID: <[email protected]>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
Newsgroups: microsoft.public.access.queries
Path: TK2MSFTNGXA01.phx.gbl
Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.access.queries:274451
NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
X-Tomcat-NG: microsoft.public.access.queries

A worthy thought, but ...

Because of some other limitations, all dates are defined as 10-character
text fields in DB2's date format (YYYY_MM_DD). IOW, they're string
variables,
not date/time variables.

This works for the 50,000+ rows where the join condition is satisfied. And
it works for all but some of the cases where the join condition is not
satisfied. And it works when I add the WHERE clause for any of the groups
that are being omitted from the result set.

:

You appear to be comparing dates using a >= and <= and that may not work
depending on the format and definition of the date columns.

-Dorian

:

I'm joining two tables using left join and noticed that some (but not
all) of
the rows from the left table were being excluded from the result set
when the
join condition is not satisfied. The following SQL returns one row, as
it
should:
SELECT Rbt.*, FullDWGPP.*
FROM Rbt LEFT JOIN FullDWGPP ON
Rbt.BILL_MO >= FullDWGPP.ROW_BEG_DT
AND Rbt.BILL_MO >= FullDWGPP.CSPI_EFF_DT
AND Rbt.BILL_MO <= FullDWGPP.ROW_END_DT
AND Rbt.BILL_MO <= FullDWGPP.CSPI_TERM_DT
AND Rbt.GROUP = FullDWGPP.GRGR_ID
AND Rbt.SUB_GROUP = FullDWGPP.SGSG_ID
AND Rbt.PLAN_CD = MID(FullDWGPP.PDPD_ID,4,4)
WHERE Rbt.GROUP = '00500530';

If I remove the WHERE clause, the same SQL returns many rows, but the
row
for group '00500530' is not amongst them. Tried all manner of
parenthesis but
I always get the same results - some but not all of the rows that
don't
statisfy the join condition are omitted. And since I'm using the MID
function
and >= conditions, I'm not able to use the query builder.

Any help would be greatly appreciated.

 

[Guest]

Hi Peter -

Thanks much for your response. Since the bug reportis in the pipeline, I'll
be content to step out of the loop. The work I was doing was some design
stage prototyping stuff; the production code will be in a different
environment so I'll not need to find a work-around.

Thanks again.
Pete

Hello Pete,

Thank you for your clarification. I agree with you the rows shall appear no
matter which join condition is used.

It seems the issue is that when using string comparsion in left join
condition, it omits some rows. Please rest assured that this has been
reported to the proper channel. If there is any update, we will let you
know.

If you'd like to solve it in a effificent manner, I recommend that you open
a Support incident with Microsoft Product Support Services so that a
dedicated Support Professional can assist with this case. If you need any
help in this regard, please let me know.

For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp

If you'd still like to continue working via the newsgroup, I want to set
your expectations that the issue might take a long time to narrow down.
During the course of troubleshooting, we may redirect you to PSS if
required. For now, please try the following and let me know how it goes:

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================


This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------

Thread-Topic: Left Join Not Properly
thread-index: AcZUxCS/1Ut0FYr/SZqHBVdXG/fhUg==
X-WBNR-Posting-Host: 12.108.117.125
From: =?Utf-8?B?UGV0ZQ==?= <[email protected]>
References: <[email protected]>

<[email protected]>
<[email protected]>

Subject: RE: Left Join Not Properly
Date: Fri, 31 Mar 2006 05:08:03 -0800
Lines: 219
Message-ID: <[email protected]>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
Newsgroups: microsoft.public.access.queries
Path: TK2MSFTNGXA01.phx.gbl
Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.access.queries:274546
NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
X-Tomcat-NG: microsoft.public.access.queries

Posted the previous before my morning coffee...

Please understand that I'm NOT saying that the JOIN condition fails for
certain rows. I'm saying that the JOIN is seemingly not even being ATTEMPTED
for certain rows.

I should have said:
Please understand that I'm NOT saying that the JOIN condition fails
UNEXPECTEDLY
for certain rows. I'm saying that the JOIN is seemingly not even being
ATTEMPTED
for certain rows. (Capital letters used in lieu of italics - I don't mean to
shout...)

Rbt Row 2 is missing from the result set entirely. If I add the WHERE
clause, without changing anything else, I get:
Rbt Row 2 + FullDWGPP row y

I should have said
Rbt Row 2 + null

To summarize (an hopefully clarify):
SELECT A.*, B.*
FROM A LEFT JOIN B
ON a bunch of conditions

should return:
A1 + Bx
A2 + null
A3 + By
A3 + Bz
A4 + null
...

Instead, it's dropping some of rows:
A1 + Bx
A3 + By
A3 + Bz
A4 + null

Meanwhile
SELECT A.*, B.*
FROM A LEFT JOIN B
ON a bunch of conditions
WHERE A.key = 2

returns the correct result:
A2 + null

and
SELECT A.*, B.*
FROM A LEFT JOIN B
ON a bunch of conditions
WHERE A.key in (1, 2, 3, 4)

returns:
A1 + Bx
A2 + null
A3 + By
A3 + Bz
A4 + null

So the question is: Why would A2 + null be in the result set when I include
a WHERE clause but fail to be in the result set when I don't? The WHERE
clause is simply specifying a subset of rows from A (where the subset
includes row A2) but the LEFT JOIN absent the WHERE clause should be using
the entire set (which also includes row A2).

Please note that I don't know what happens under the covers with the WHERE
clause. I don't know if it's filtering A before the JOIN or if it filtering
the JOIN once that's completed. Either way, row A2 + null should be in the
result set with or without the WHERE clause...

Thanks again,
Pete

Hi Peter -

I'm as sure as I can be that the choice of representation for dates is not
the issue. I'm simply comparing some strings that happen to encode some dates
in YYYY-MM-DD format. ANd I know that the rows in question have valid data
because I've looked and because the inclusion of a simple WHERE clause shows
the comparisons to be working as expected.

Please understand that I'm NOT saying that the JOIN condition fails for
certain rows. I'm saying that the JOIN is seemingly not even being ATTEMPTED
for certain rows.

Here's what I expected from the LEFT JOIN:
Rbt Row 1 + [FullDWGPP row x | null]
Rbt Row 2 + [FullDWGPP row y| null]
Rbt Row 3 + [FullDWGPP row z | null]
...

The success or failure of the JOIN conditions (including the date
comparisons) would determine whether the right side of the resulting row is
a) FullDWGPP data or b) null but should have no bearing on the presence or
absence of rows themselves.

Here's what I get, though:
Rbt Row 1 + FullDWGPP row x
Rbt Row 3 + FullDWGPP row z

Rbt Row 2 is missing from the result set entirely. If I add the WHERE
clause, without changing anything else, I get:
Rbt Row 2 + FullDWGPP row y

Pete

:

Hello,

It seems string comparison is a little different from date comparison. You
may want to convert the text to date by using CVDate/left/mid/right
functions before comparision.

Also, make sure the string format is consistent for the omitted records.


Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------
Thread-Topic: Left Join Not Properly
thread-index: AcZUF55aEEUGlOEqRY+dqR1MIL7Nww==
X-WBNR-Posting-Host: 12.108.117.125
From: =?Utf-8?B?UGV0ZQ==?= <[email protected]>
References: <[email protected]>
<[email protected]>
Subject: RE: Left Join Not Properly
Date: Thu, 30 Mar 2006 08:33:04 -0800
Lines: 43
Message-ID: <[email protected]>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
Newsgroups: microsoft.public.access.queries
Path: TK2MSFTNGXA01.phx.gbl
Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.access.queries:274451
NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
X-Tomcat-NG: microsoft.public.access.queries

A worthy thought, but ...

Because of some other limitations, all dates are defined as 10-character
text fields in DB2's date format (YYYY_MM_DD). IOW, they're string
variables,
not date/time variables.

This works for the 50,000+ rows where the join condition is satisfied. And
it works for all but some of the cases where the join condition is not
satisfied. And it works when I add the WHERE clause for any of the groups
that are being omitted from the result set.

:

You appear to be comparing dates using a >= and <= and that may not work
depending on the format and definition of the date columns.

-Dorian

:

I'm joining two tables using left join and noticed that some (but not
all) of
the rows from the left table were being excluded from the result set
when the
join condition is not satisfied. The following SQL returns one row, as
it
should:
SELECT Rbt.*, FullDWGPP.*
FROM Rbt LEFT JOIN FullDWGPP ON
Rbt.BILL_MO >= FullDWGPP.ROW_BEG_DT
AND Rbt.BILL_MO >= FullDWGPP.CSPI_EFF_DT
AND Rbt.BILL_MO <= FullDWGPP.ROW_END_DT
AND Rbt.BILL_MO <= FullDWGPP.CSPI_TERM_DT
AND Rbt.GROUP = FullDWGPP.GRGR_ID
AND Rbt.SUB_GROUP = FullDWGPP.SGSG_ID
AND Rbt.PLAN_CD = MID(FullDWGPP.PDPD_ID,4,4)
WHERE Rbt.GROUP = '00500530';

If I remove the WHERE clause, the same SQL returns many rows, but the
row
for group '00500530' is not amongst them. Tried all manner of
parenthesis but