I have the following three tables

Table1 ID, Name (multiple records)
Table2 TodayDate (single record)
Table3 T3ID, T3Date, T3ForeignKey (to Table1.ID)

I want to select all items from table3, joining table1 to get the name,
where table3.T3Date = Table2 OR table3.T3Date = NULL.

IOW, I want to JOIN table2 with no ON clause, since my logic will be all on
the WHERE clause. DBISAM (V3.30) does not allow that. How can it be coded?

SELECT Name
from Table1
Join Table2
Join Table3 on Table3.ForeignKey = Table1.ID
WHERE ((Table3.T3Date = Table2.TodayDate) or (Table3.T3Date = NULL))

TIA

Robert

"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote on Mon, 19 Jun 2006 16:43:05 -0400

>I have the following three tables
>
>Table1 ID, Name (multiple records)
>Table2 TodayDate (single record)
>Table3 T3ID, T3Date, T3ForeignKey (to Table1.ID)
>
>I want to select all items from table3, joining table1 to get the name,
>where table3.T3Date = Table2 OR table3.T3Date = NULL.
>
>IOW, I want to JOIN table2 with no ON clause, since my logic will be all on
>the WHERE clause. DBISAM (V3.30) does not allow that. How can it be coded?
>
>SELECT Name
>from Table1
>Join Table2
>Join Table3 on Table3.ForeignKey = Table1.ID
>WHERE ((Table3.T3Date = Table2.TodayDate) or (Table3.T3Date = NULL))
>
>TIA
>
>Robert
>
>
Robert


Does the following work?

SELECT Name
from Table1, Table2
Join Table3 on Table3.ForeignKey = Table1.ID
WHERE ((Table3.T3Date = Table2.TodayDate) or (Table3.T3Date = NULL))

or, if TodayDate really is today, do you need Table2 at all?  Can't you use CURRENT_DATE in your WHERE?

HTH


Jeff



--
Jeff Cook
Aspect Systems Ltd
Phone: +64-9-424 5388
Skype: jeffcooknz
www.aspect.co.nz

"Jeff Cook" <jeffc@aspect.co.nz> wrote in message
news:42186BA5-9D4C-4BDE-A548-EB35CC8CFE74@news.elevatesoft.com...
>
> Does the following work?
>
> SELECT Name
> from Table1, Table2

It does indeed work. Do you know if there is a way to use a join to
accomplish the same?

> Join Table3 on Table3.ForeignKey = Table1.ID
> WHERE ((Table3.T3Date = Table2.TodayDate) or (Table3.T3Date = NULL))
>
> or, if TodayDate really is today, do you need Table2 at all? Can't you use
> CURRENT_DATE in your WHERE?
>

No, it is not today's date. The test is pseudocode is "date is either null
or equal to the date stored in the single-record table2".

Robert

"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote on Mon, 19 Jun 2006 17:23:00 -0400

>
>"Jeff Cook" <jeffc@aspect.co.nz> wrote in message
>news:42186BA5-9D4C-4BDE-A548-EB35CC8CFE74@news.elevatesoft.com...
>>
>> Does the following work?
>>
>> SELECT Name
>> from Table1, Table2
>
>It does indeed work. Do you know if there is a way to use a join to
>accomplish the same?
>


Robert


I don't think so - the "Cartesian" join is documented with that syntax in the Help - can't say I've used it myself - just happened to read about it yesterday!

Why do you need to do it another way?


Cheers

Jeff

--
Jeff Cook
Aspect Systems Ltd
Phone: +64-9-424 5388
Skype: jeffcooknz
www.aspect.co.nz

"Jeff Cook" <jeffc@aspect.co.nz> wrote in message
news:2BE58B50-95BE-406B-9534-7C53379B5EEB@news.elevatesoft.com...
>
>
> I don't think so - the "Cartesian" join is documented with that syntax in
> the Help - can't say I've used it myself - just happened to read about it
> yesterday!
>
> Why do you need to do it another way?
>

Just to use the most current syntax.

Robert

Robert,

<< I want to select all items from table3, joining table1 to get the name,
where table3.T3Date = Table2 OR table3.T3Date = NULL.

IOW, I want to JOIN table2 with no ON clause, since my logic will be all on
the WHERE clause. DBISAM (V3.30) does not allow that. How can it be coded?
>>

Like this:

SELECT Name
from Table1 JOIN Table3 on Table3.ForeignKey = Table1.ID, Table2
WHERE ((Table3.T3Date = Table2.TodayDate) or (Table3.T3Date = NULL))

--
Tim Young
Elevate Software
www.elevatesoft.com