Selecting Specific Row when Several Match

[dismissedasdrone]

Hi, this is my first post and I'm hoping someone can point me in the right direction with an issue I'm having related to MySQL.

Here are the tables:  (example

[b]tbl_info[/b]
[code]
id, fk_data_id status
[/code]

[b]tbl_data[/b]
[code]
id value fk_picture_id
[/code]

[b]tbl_picture[/b]
[code]
id
picture_id
path
[/code]

so if I run something like

[code]
SELECT i.id as one, d.value as two, p.path as three
FROM tbl_info i, tbl_data d, tbl_picture p
WHERE i.fk_data_id = d.id
AND d.fk_picture_id = p.id
[/code]

This works fine.  The trouble I'm having is that there may be multiple records in tbl_picture that have the same picture_id (non-unique column).  There is an auto-incrementing column (id) in the table.  Is there a way to add something to my query that would only return the tbl_picture.path with the highest id value? (the assumption being that the higher/highest value would be the most recent)

I'm sure I did a terrible job of explaining this, but hopefully somebody can help me out.

Thanks in advance,

*

[fenway]

You need a subquery / derived table to do that query first, and then only use those uids for the joins.

[dismissedasdrone]

Thanks for your reply. 

I was hoping that wasn't going to be the case.  My real world situation involves about 6 times the tables and relationships so it seems like I would basically need to rebuild the majority of the first SELECT into the sub-statement...since I'm guessing a simple "SELECT id FROM tbl_picture ORDER by id DESC" wouldn't be specific enough...

Thanks again.

*