On 20 Mar, 10:55, "Bob Bedford" <b...@bedford.com> wrote:
> Hi all,
>
> Description of my tables:
>
> table1
> idfield1
> namefield1
>
> table2
> idlink
> idfield1
> idfield3
>
> table3
> idfield3
> namefield3
>
> table1.idfield1 = table2.idfield1
>
> On every record of table1 they may be one or more record on table 2.
>
> Now I've tho find all record from table1 in wich the last value (ordered by
> idlink) looking for a value in namefield3.
>
> I've tried this:
> select table2.idfield1, max(idlink)
> from table2
> inner join table3 on table2.idfield3 = table3.idfield3
> where table3.namefield3 like '%searchtext%'
> group by table2.idfield1
>
> max(idlink) return the idlink where searchtext is found. Instead I want only
> the record where max(idlink) has the idfield2 to the searched value.
> example values:
> I'm looking for idfield3 = 5
> For those records in table2 (idlink,idfield1,idfield3)
> 1, 10, 3
> 2, 10, 5
> 3, 10, 2 //(last value for idfield1 10 is 2 so not in result)
> 4, 15, 5
> 5, 15, 8 //(last value for idfield1 15 is 8, so not in result)
> 6, 8, 3
> 7,8,5 //last value of idfield1 8 is 5, so I want it)
>
> How to do so ?
>
> This is part of a subquery, I can only have 1 result.

Search this forum for "strawberry query"