I am trying to create a document control db. Currently I have two tables:

tbl_sod : contains the name of the document and other various document specific details

tbl_sod_status: contains who the document was sent to and the rev of the document that was sent.

I came up with the following SQL

SELECT * FROM tbl_sod LEFT JOIN tbl_sod_status ON tbl_sod.sod_id=tbl_sod_status.sod_id

problem: It reports back all occurrances of the document regardless of the revision level.

I have tried inserting a limit but then it only gives me a single record.

tbl_sod
sod_id Drawing Description
1 111 Drawing A
2 222 Drawing B
3 333 Drawing C

tbl_sod_status
status_id sod_id Rev
1 1 A
2 2 A
3 3 A
4 1 B
5 3 B

result that I am looking for:

1 111 Drawing A B
2 222 Drawing B A
3 333 Drawing C B

Making some small progress here.

I know have a query that gives me the max value of each document revision

SELECT tbl_sod_status.sod_id, MAX( tbl_sod_status.rev )
FROM tbl_sod_status
GROUP BY tbl_sod_status.sod_id

but now I need to do a left join with the parent table

SELECT * FROM tbl_sod.

Okay, so with a little more sweat and tears, I figured it out.

$sql='SELECT * FROM tbl_sod LEFT JOIN ('.
'SELECT tbl_sod_status.sod_id, MAX( tbl_sod_status.rev ) AS max_rev '.
'FROM tbl_sod_status '.
'GROUP BY tbl_sod_status.sod_id '.
') AS qry_max ON tbl_sod.sod_id = qry_max.sod_id ';