jav_vbulletin3_import19419
New Member
Sorry I couldn't explain this better in the title. Here is essentially what I need to accomplish:\[code\]Entity table: entity_idBenchHistory table: entity_id, bench_id, bench_effective_dateSummary table: entity_id, effective_date\[/code\]That is the database layout in a nutshell. This query finds each entity's \[code\]effective_date\[/code\] from the \[code\]Summary\[/code\] table. It also needs to find the \[code\]bench_id\[/code\] for that particular date, by looking through the \[code\]BenchHistory\[/code\] table and finding the maximum \[code\]bench_effective_date\[/code\] that is less than \[code\]effective_date\[/code\] where \[code\]BenchHistory.entity_id = Entity.entity_id\[/code\].Here is what I have:\[code\]SELECT Entity.entity_id Summary.effective_date BenchHistory.bench_idFROM EntityJOIN Summary ON Entity.entity_id = Summary.entity_idJOIN BenchHistory ON Entity.entity_id = BenchHistory.entity_id\[/code\]Pretty simple, the bench part is what I'm having trouble with. How do I select only one \[code\]BenchHistory.bench_id\[/code\], that must be the most recent relative to \[code\]Summary.effective_date\[/code\]? For clarity, Each entity has many corresponding \[code\]effective_dates\[/code\] and many corresponding \[code\]bench_ids\[/code\], but only one \[code\]bench_id\[/code\] can be "valid" at a time (the most recent one). I am trying to find each row's "valid" \[code\]bench_id\[/code\] depending on that row's \[code\]effective_date\[/code\]. I need to do this by determining which \[code\]bench_id\[/code\] has a \[code\]bench_effective_date\[/code\] less than \[code\]effective_date\[/code\].Here is an example of a query that I have, maybe it will be easier to visualize then.\[code\]SELECT Entity.entity_id BenchHistory.bench_id Summary.effective_date BenchHistory.bench_effective_dateFROM EntityJOIN Summary ON Entity.entity_id = Summary.entity_idJOIN BenchHistory ON Entity.entity_id = BenchHistory.entity_id\[/code\]This will give output like:\[code\]entity_id bench_id effective_date bench_effective_date1 120 1/31/2011 6/30/20031 121 1/31/2011 3/22/20051 122 1/31/2011 11/03/20081 123 1/31/2011 1/21/20111 124 12/30/2010 5/15/20101 125 12/30/2010 10/06/2010\[/code\]What I want to grab, is just \[code\]bench_id\[/code\] 123 for 1/31, as it is the most recent bench for \[code\]entity_id = 1\[/code\] and \[code\]bench_id\[/code\] 125 for 12/30, etc. So a result set:\[code\]entity_id bench_id effective_date bench_effective_date1 123 1/31/2011 1/21/20111 125 12/30/2010 10/06/2010\[/code\]Thank you, sorry if this is an easy question but I have been working on it for 6 hours trying all sorts of subqueries, aggregates, GROUP BY, and whatever. I am not that experienced with SQL.