Tracey
is JOBLINE.Job Date ENTRY.EntryDate Indexed?
Also you want all 'all entries' and 'all Job Lines'?
if so
SELECT
'JobLine', JOBLINE.JobDate, JOBLINE.JobLineID, Job.MachineID
FROM
JOBLINE JOIN
JOB ON JOBLINE.JobID = JOB.JobID
WHERE
JOBLINE.JobDate BETWEEN Date1 AND Date2
UNION
SELECT
'Entry', ENTRY.EntryDate, ENTRY.EntryID, ENTRY.MachineID,
FROM
ENTRY
WHERE
ENTRY.EntryDate BETWEEN Date1 AND Date2
----- Original Message -----
From: "Tracey Maule" <[EMAIL PROTECTED]>
To: "Multiple recipients of list delphi" <[EMAIL PROTECTED]>
Sent: Thursday, July 31, 2003 9:37 AM
Subject: [DUG]: Interbase SQL
The usual topic im afraid...
I have the following tables:
MACHINE
machineid
ENTRY
EntryID
MachineID
EntryDate
Quantity
StartQuantity
EndQuantity
JOB
JobID
MachineID
JOBLINE
JoblineID
JobID
JobDate
JobLineCost
I need to select all entries between 2 dates, and all joblines between 2
dates, for every machine where there is an entry in one or the other.
rather than selecting all machines, then doing individual selects from each
table for each machine, it was suggested that i could select the records,
put them into temp tables to minimize the query count.
Something like :
Select stuff
from table
Outer join machine
where date_conditions
into temptable
However I am told Interbase (Firebird1.0) does not do temp-tables.
At this point it looks like my best choice is:
Select ID from machine where company = :company
Then
(while not eof)
Select records from entry
where dateconditions = blah
and machineid = query1MachineID
And then
Select records from jobline
Join job on blah
Where dateconditions = blah
And machineid = query1machineid
(eof)
This doesnt seem very efficient to me. Can any of the SQL gurus come up
with a more efficient way of doing this?
Tracey
Software Developer / Web Master
Logis
[EMAIL PROTECTED]
(025) 213-1065
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
To UnSub, send email to: [EMAIL PROTECTED]
with body of "unsubscribe delphi"
Web Archive at: http://www.mail-archive.com/delphi%40delphi.org.nz/
