[thelist] subselect query?

[Peter Brunone (EasyListBox.com)]

Hi Dallas,

   Can you tell us (a) what kind of DBMS you are using and (b) the structure of the tables involved?

Cheers,

Peter

 From: "Dallas Cahker" <christmasfruitcake at gmail.com>
Sent: Monday, March 06, 2006 12:47 PM
To: thelist at lists.evolt.org
Subject: [thelist] subselect query? 

I have a problem. My problem is this. I have a database of scans, the
scans are linked together by an invoice number, there might be 8-12 scans
per invoice, I need to get the invoice number of the scan that left area 20
on a certain date, after that I need to get the area it is currently in.
Can someone help me with getting the subselect correct for that, or is there
a better way to do it, even if it is 650,000 records.

SELECT B.RDSTAT, COUNT(*) FROM ORPRDETL A, ORPRDETL B WHERE A.RDSTAT = 200
AND B.\"RDORD#\" = A.\"RDORD#\" AND B.RDENDX = '2006-02-27' AND B.RDSTRX <>
'1940-01-01' AND B.RDSTAT = (SELECT MAX(C.RDSTAT) FROM ORPRDETL C WHERE
C.\"RDORD#\" = B.\"RDORD#\") GROUP BY B.RDSTAT - I dont get any invoice
numbers so I cant do any checks to see if the data is accurate.
--