Left Join query

[Dave]

To build a grid, all the distinct rows from T1 are required,
and only those from T2 which fall btn 2003-09-11 and 2003-09-18.
In the following example:
A is included because it is within the date range
C is inclucded because the date is null
B is excluded because it is outside the date range

Any suggestions on how to get the desired result would be greatly
appreciaed!

T1 T2

ID ID DATES

A A 2003-09-10

B B 2003-09-10

C A 2003-09-11

Desired Result

A 2003-09-11

B

C

Select T1.ID from T1 left join T2 on T1.ID = T2.ID

where (T2.DATES>=' 2003-09-11' and T2.DATES<=' 2003-09-18') or T2.DATES
is null

Result

A 2003-09-11

C

[BDR]

Afaik, once you've included a column to be listed in your statement, you
can't 'selectively' eliminate it (or force a display null) in a selected
row result. The way I work around this is do a select statement for all
distinct rows in T1, then another below it in programming code to select
the desired date range output. Not a subselect, but two selects using
two simultaneous connections to mysql. (But if you're using mysql>4 I
think a subselect would certainly do the trick though).

IE:
Select Distinct T1.ID from T1
while (T1.ID)
print t1.id...
Select T2.DATES where T2.ID=T1.ID AND ((T2.DATES>='2003-09-11' and

T2.DATES<='2003-09-18') or T2.DATES is null)
print T2.DATES...

Something on this order, depending on what your pgming with.

Dave wrote:

To build a grid, all the distinct rows from T1 are required,
and only those from T2 which fall btn 2003-09-11 and 2003-09-18.
In the following example:
A is included because it is within the date range
C is inclucded because the date is null
B is excluded because it is outside the date range

Any suggestions on how to get the desired result would be greatly
appreciaed!

T1 T2

ID ID DATES

A A 2003-09-10

B B 2003-09-10

C A 2003-09-11

Desired Result

A 2003-09-11

B

C

Select T1.ID from T1 left join T2 on T1.ID = T2.ID

where (T2.DATES>=' 2003-09-11' and T2.DATES<=' 2003-09-18') or T2.DATES
is null

Result

A 2003-09-11

C

[BDR]

Afaik, once you've included a column to be listed in your statement, you
can't 'selectively' eliminate it (or force a display null) in a selected
row result. The way I work around this is do a select statement for all
distinct rows in T1, then another below it in programming code to select
the desired date range output. Not a subselect, but two selects using
two simultaneous connections to mysql. (But if you're using mysql>4 I
think a subselect would certainly do the trick though).

IE:
Select Distinct T1.ID from T1
while (T1.ID)
print t1.id...
Select T2.DATES where T2.ID=T1.ID AND ((T2.DATES>='2003-09-11' and

T2.DATES<='2003-09-18') or T2.DATES is null)
print T2.DATES...

Something on this order, depending on what your pgming with.

Dave wrote:

To build a grid, all the distinct rows from T1 are required,
and only those from T2 which fall btn 2003-09-11 and 2003-09-18.
In the following example:
A is included because it is within the date range
C is inclucded because the date is null
B is excluded because it is outside the date range

Any suggestions on how to get the desired result would be greatly
appreciaed!

T1 T2

ID ID DATES

A A 2003-09-10

B B 2003-09-10

C A 2003-09-11

Desired Result

A 2003-09-11

B

C

Select T1.ID from T1 left join T2 on T1.ID = T2.ID

where (T2.DATES>=' 2003-09-11' and T2.DATES<=' 2003-09-18') or T2.DATES
is null

Result

A 2003-09-11

C

[BDR]

Hate responding to my own posts... but I didn't stay at a holiday inn
express last night. This one is much easier than I made it sound
originally. (I just finished a monster multi-join prob yesterday that
threw my head for a loop on this simple one that you posted)..

Move your where condition to the on condition...

SELECT T1.ID, T2.DATES
FROM T1
left join T2
on T1.ID = T2.ID
and ((T2.DATES >= ' 2003-09-11' and T2.DATES <= ' 2003-09-18')
or T2.DATES is null)

ID DATES
A 2003-09-11
B NULL
C NULL
BDR wrote:

Afaik, once you've included a column to be listed in your statement, you
can't 'selectively' eliminate it (or force a display null) in a selected
row result. The way I work around this is do a select statement for all
distinct rows in T1, then another below it in programming code to select
the desired date range output. Not a subselect, but two selects using

....snip...

[BDR]

Hate responding to my own posts... but I didn't stay at a holiday inn
express last night. This one is much easier than I made it sound
originally. (I just finished a monster multi-join prob yesterday that
threw my head for a loop on this simple one that you posted)..

Move your where condition to the on condition...

SELECT T1.ID, T2.DATES
FROM T1
left join T2
on T1.ID = T2.ID
and ((T2.DATES >= ' 2003-09-11' and T2.DATES <= ' 2003-09-18')
or T2.DATES is null)

ID DATES
A 2003-09-11
B NULL
C NULL
BDR wrote:

Afaik, once you've included a column to be listed in your statement, you
can't 'selectively' eliminate it (or force a display null) in a selected
row result. The way I work around this is do a select statement for all
distinct rows in T1, then another below it in programming code to select
the desired date range output. Not a subselect, but two selects using

....snip...

[Dave]

BDR,
The query actually contains 6 tables and 5 joins, but that one change,
moving the where condition to the on conditon did the trick!
Thanks for your help.

Dave

"BDR" <jo*@noemail.com> wrote in message
news:3F**************@noemail.com...

Hate responding to my own posts... but I didn't stay at a holiday inn
express last night. This one is much easier than I made it sound
originally. (I just finished a monster multi-join prob yesterday that
threw my head for a loop on this simple one that you posted)..

Move your where condition to the on condition...

SELECT T1.ID, T2.DATES
FROM T1
left join T2
on T1.ID = T2.ID
and ((T2.DATES >= ' 2003-09-11' and T2.DATES <= ' 2003-09-18')
or T2.DATES is null)

ID DATES
A 2003-09-11
B NULL
C NULL
BDR wrote:

Afaik, once you've included a column to be listed in your statement, you
can't 'selectively' eliminate it (or force a display null) in a selected
row result. The way I work around this is do a select statement for all
distinct rows in T1, then another below it in programming code to select
the desired date range output. Not a subselect, but two selects using

...snip...

[Dave]

BDR,
The query actually contains 6 tables and 5 joins, but that one change,
moving the where condition to the on conditon did the trick!
Thanks for your help.

Dave

"BDR" <jo*@noemail.com> wrote in message
news:3F**************@noemail.com...

Hate responding to my own posts... but I didn't stay at a holiday inn
express last night. This one is much easier than I made it sound
originally. (I just finished a monster multi-join prob yesterday that
threw my head for a loop on this simple one that you posted)..

Move your where condition to the on condition...

SELECT T1.ID, T2.DATES
FROM T1
left join T2
on T1.ID = T2.ID
and ((T2.DATES >= ' 2003-09-11' and T2.DATES <= ' 2003-09-18')
or T2.DATES is null)

ID DATES
A 2003-09-11
B NULL
C NULL
BDR wrote:

Afaik, once you've included a column to be listed in your statement, you
can't 'selectively' eliminate it (or force a display null) in a selected
row result. The way I work around this is do a select statement for all
distinct rows in T1, then another below it in programming code to select
the desired date range output. Not a subselect, but two selects using

...snip...