I know I ask too many SQL questions, but I try not to ask the same one
twice. I am just starting to use SQL more now that I switched to C/S. I know
this is a JOIN in SQL, but I haven't done this before.

I have two tables:

Table1 (master table)
 PrimaryKey
 SearchColumn1
 ManyOtherFieldsIwon'tuse

Table2 (detail table)
 Table1PrimaryKey
 DateColumn
 OtherColumn

What I need as a result table is: Table2.DateColumn,Table2.OtherColum

but only for Table1.SearchColumn=SOMEVALUE (master condition)
and Table2.DateColumn between DATEVALUE1 DATEVALUE2 and
Table1.PrimaryKey=Table2.Table1PrimaryKey (detail condition)

With Tables I would just do:
Table1.Filter := SearchColumn=SOMEVALUE
while not Table1.EOF do
begin
  Table2.Filter := Table1PrimaryKey=Table1.PrimaryKey and (DateColumn
between DATEVALUE1 DATEVALUE2);
 while Table2.EOF do
 begin
     //This is a valid matching record now - process
Table2.DateColumn,Table2.OtherColumn
    Table2.Next;
 end;
  Table1.Next;
end;

Thanks for any help with this. I promise to learn and study joins, but right
now my SQL for Dummies book looks a bit above my head.

-Johnnie

Johnnie

I'm also an SQL novice but here goes

SELECT Table2.DateColumn,Table2.OtherColum
FROM Table1
JOIN Table2 ON Table2.Table1PrimaryKey = Table1.PrimaryKey
WHERE
Table2.DateColumn BETWEEN DATEVALUE1 AND DATEVALUE2


Try it in DBSys and see what happens

Roy Lambert

Roy Lambert <roy.lambert@skynet.co.uk> wrote in news:35DCF68B-E751-46C7-
BCFF-7C76FC973103@news.elevatesoft.com:

> SELECT Table2.DateColumn,Table2.OtherColum
> FROM Table1
> JOIN Table2 ON Table2.Table1PrimaryKey = Table1.PrimaryKey
> WHERE
> Table2.DateColumn BETWEEN DATEVALUE1 AND DATEVALUE2

If you're using this in a master-detail relationship with 2 TDBISAMQuery's,
you can use:

SELECT DateColumn,OtherColum
FROM Table2
WHERE Table1PrimaryKey = :PrimaryKey /* <= this must be the real fieldname
in table1 */
AND DateColumn BETWEEN DATEVALUE1 AND DATEVALUE2

and set Query2.MasterSource to the datasource that is following Query1.

Each time Query1 moves (as the user changes his selection criteria) Query2
will be closed and reopened with the appropriate set of records.

--
Chris

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:35DCF68B-E751-46C7-BCFF-7C76FC973103@news.elevatesoft.com...
> Johnnie
>
> I'm also an SQL novice but here goes
>
> SELECT Table2.DateColumn,Table2.OtherColum
> FROM Table1
> JOIN Table2 ON Table2.Table1PrimaryKey = Table1.PrimaryKey
> WHERE
> Table2.DateColumn BETWEEN DATEVALUE1 AND DATEVALUE2

How would I add the other (master) condition?
Table1.SearchField=SEARCHSTRING

Johnnie

SELECT Table2.DateColumn,Table2.OtherColum
FROM Table1
JOIN Table2 ON Table2.Table1PrimaryKey = Table1.PrimaryKey
WHERE
Table2.DateColumn BETWEEN DATEVALUE1 AND DATEVALUE2
AND
Table1.SearchField=SEARCHSTRING


Roy Lambert

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:A51AC9F2-556C-45F0-8D9D-700CA7CF95CE@news.elevatesoft.com...
> SELECT Table2.DateColumn,Table2.OtherColum
> FROM Table1
> JOIN Table2 ON Table2.Table1PrimaryKey = Table1.PrimaryKey
> WHERE
> Table2.DateColumn BETWEEN DATEVALUE1 AND DATEVALUE2
> AND
> Table1.SearchField=SEARCHSTRING

Thanks Roy. I'll give it a try.