469,081 Members | 1,814 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,081 developers. It's quick & easy.

Left Join query

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


Jul 19 '05 #1
6 4666
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


Jul 19 '05 #2
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


Jul 19 '05 #3
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...

Jul 19 '05 #4
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...

Jul 19 '05 #5
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...

Jul 19 '05 #6
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...

Jul 19 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Marek Lewczuk | last post: by
3 posts views Thread by Ian Boyd | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.