SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-28-2008, 09:26 AM
| ||||
| ||||
 max record 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.      |
|
02-28-2008, 09:26 AM
| ||||
| ||||
| Re: max record 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" |
Linear Mode
