Looking for some help with JOIN logic

[NDayave]

Good Day,

I have a number of tables that need to be joined to gether and up to now everything has been fine. I now need to exclude old files that are identified by a sequence number; newer records have a higher number.

The joins currently look like this:

FROM a
    INNER JOIN b
        ON(a.resource_id = b.resource_id)
    INNER JOIN c
        ON(a.resource_id = c.apar_id)
    INNER JOIN  d
        ON(a.client = d.client)
    INNER JOIN e
        ON( a.resource_id = e.resource_id
    AND a.client = e.client )
 

The sequence number is held in table b and I want to return only the record from table a that corresponds to the highest sequence number. As all records have a sequence number (starting at 1), I cannot work out how to exclude only those that have a superceeding record.

For Example:

With a.resource_id = 123456

Record 1 (To be excluded due to Record 2):
b.resource_id = 123456
b.sequence_no = 1

Record 2 (To be returned):
b.resource_id = 123456
b.sequence_no = 2

Whereas for records with no superceeding sequence_no:

With a.resource_id = 654321

Record 1 (To be returned):
b.resource_id = 654321
b.sequence_no = 1


I hope that makes sense.

All help is much appreciated.

[NDayave]

Never mind, sorted it with help from here.

Final syntax was (Take note of the WHERE clause addition at the bottom):

FROM a 
    INNER JOIN (b AS b1
                LEFT OUTER JOIN b AS b2
                    ON(b1.resource_id = b2.resource_id
                        AND b1.sequence_no < a2.sequence_no))
        ON(a.resource_id = b.resource_id) 
    INNER JOIN c 
        ON(a.resource_id = c.apar_id) 
    INNER JOIN  d 
        ON(a.client = d.client) 
    INNER JOIN e 
        ON( a.resource_id = e.resource_id 
    AND a.client = e.client )
 
WHERE b2.resource_id IS NULL