Using tables I would define a master/detail relationship, set filters an
iterate through the master records to print a report.

MASTER.Filter := 'filter condition'
DETAIL.Filter := 'filter condition'

I am converting this to SQL because the table operations are very slow in
C/S since there are so many situations where there are no detail records.

I want to get a list of master records only when they have at least one
detail record matching some criteria using SQL, which should speed up my
reporting immensely.  I haven't done too many JOINs in my program to
understand how to code this, so any help would be appreciated. I assume I
need to do a COUNT() or test to see if a result set is empty.

After I get the master records in a query, I'll use those for my detail
linkage - which should make the report much faster.

Thanks,

-Johnnie

SELECT * FROM MASTER WHERE (MASTERCONDITION)

Detail link: MASTER.ID=DETAIL.MID

Detail condition: (Detail.Date>'2006-08-01')

"Johnnie Norsworthy" <jln206@verizon.net> wrote in message
news:8E9437AC-955D-4FDE-B390-62034E33706E@news.elevatesoft.com...
>
> I want to get a list of master records only when they have at least one
> detail record matching some criteria using SQL, which should speed up my

You can do a

SELECT DISTINCT MID into memory\temp where detail condition;
CREATE INDEX ByID (MID) on memory\temp; // not sure about syntax here, check
out the doc
SELECT .... FROM MASTER
JOIN MEMORY\TEMP ON MID = ID
WHERE master condition;

This will produce the master fields you need in a dataset, containing only
only the ones that have at least one detail. At an appropriate time, it is
good practice to DRP TABLE IF EXISTS MEMORY\TEMP.

Robert

Johnnie


I'd also look at joining the master to the detail and in addition to the master condition make sure you include the master id in the select list and have a condition

MASTERID IS NOT NULL

Might be faster, might be slower, can't say without trying it.

Roy Lambert

Johnnie,

<< I want to get a list of master records only when they have at least one
detail record matching some criteria using SQL, which should speed up my
reporting immensely.  I haven't done too many JOINs in my program to
understand how to code this, so any help would be appreciated. I assume I
need to do a COUNT() or test to see if a result set is empty.

After I get the master records in a query, I'll use those for my detail
linkage - which should make the report much faster. >>

Actually, it's just as fast to just get the whole mess.  Otherwise, you're
making two passes at the same data:

SELECT * FROM MASTER INNER JOIN DETAIL ON MASTER.ID=DETAIL.MID
WHERE (MASTERCONDITION) AND (Detail.Date>'2006-08-01')

That will do the trick since any master records that don't have a detail
record will have a NULL DETAIL.DATE column, thus allowing the WHERE clause
to weed them out.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:E6256A19-32C5-4CEA-A68E-41F6DEEC2E49@news.elevatesoft.com...
> Actually, it's just as fast to just get the whole mess.  Otherwise, you're
> making two passes at the same data:
>
> SELECT * FROM MASTER INNER JOIN DETAIL ON MASTER.ID=DETAIL.MID
> WHERE (MASTERCONDITION) AND (Detail.Date>'2006-08-01')
>
> That will do the trick since any master records that don't have a detail
> record will have a NULL DETAIL.DATE column, thus allowing the WHERE clause
> to weed them out.

This is what I'll do after I can get the time to change the report design
from master/detail to using grouping of a master field.

For now I am just using the temporary memory table approach to create a
master subset and the report can run faster than it has been.

Thanks,

-Johnnie