Find lastest document

[dgkindy]

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
Quote:
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

 

[pbaldy]

It sounds like you want something like this:

http://www.baldyweb.com/LastValue.htm

but using your ID field instead of the date field I use in my example.