Complex Query

[eluser]jleequeen[/eluser]
Hi,

I have a query that is a little too complex for me. I can't seem to figure it out. I tried using Active Record but this might need to be a custom query. Here is my scenario.

I have three tables:

systems: id, blah blah blah...

system_basins: id, basin_id, year, percent

basins: id, name

--------------------------------------------------------------

Basically what I want to do is pull out the following info:

[system id], [basin name], [rest of system info]

Seems easy, but my problem is that the system_basins table has multiple years of data, and can also have multiple basins per year per system. But what I want to do is pull out the basin of the last reported year that has the highest percentage. The percentage field is there to determine what percentage of area lies within a basin. So if the system were to be in only one basin, that would be 100%.

I'm trying to get a primary basin so that the user can do sorting on that information. I'm just not sure quite how to do a query of this magnitude. Maybe this type of query is not possible, i'm not sure. It all hinges on getting the last reported year and within that the highest percentage of area in the system_basins table.

Any ideas?

[eluser]henrihnr[/eluser]
here's what i think might suit your need

Code:

SELECT `s.id`, `b.name`, MAX(year), MAX(percent), `rest_of_system_info`
FROM system s INNER JOIN system_basin sb ON s.id = sb.id
    INNER JOIN basin ON sb.basin_id = basin.id
GROUP BY sb.id

hope this help