Compare 2 lines in a mysql db

[rasmas]

HI All

The data comes from a custom data base, that our accounting software uses, that I have imported into MYSQL so I can run reports and use the info.

it lay out is as follows
TYPE - Order or invoice
DOCNO - Order number or Invoice number
ORDERNO - Order Number
ODATE - Order Date
RDATE - Delivery Date
IDATE - Invoice date

Line 1 will look as follows
O 090000 090000 25/2/2005 28/2/2005 30/12/1899

Line 2 will look as follows
I 150849 090000 30/12/1899 30/12/1899 9/3/2005

I need to compare ODATE to RDATE to IDATE

now ODATE TO RDATE is easy as they are on the same line but I need to then compare IDATE from line 2.

these lines do not follow in the database

so I need select the order number where the type is O and then serch through till I find the same order number but where the type is I and the compare RDATE from type O to the IDATE from they Type I

Any Help would be great

Thanks

[laserlight]

Could you do a restructuring of the database schema?

It is clear that there is a one-one or one-many relationship between orders and invoices. In the current schema, the odate and rdate for the invoice is identical to the respective order, but that produces duplication. Further duplication occurs for orders since the docno and orderno would be the same. A possible alternative schema would be:

Table: orders
orderno
odate
rdate

Table: invoices
invoiceno
orderno
idate

[rasmas]

HI

I know the problems with the schema but i can not change it. it is a direct import from other database that happens every night to update the days transaction. so i have to write somthing with the schema I have.

Thanks