Re: Displaying results either side of an Query?

Steve Lawson wrote: > Yo, > Sounds like you need to do a join in your query. > > I assume you have some sort of (id) column and you want to display results > from ((your query id) - 15) to ((your query id) +15)? > > Here is an example. Hopefully this is what your trying to do... Let's say > you have a mysql table (mytable) with 2 columns (id) and (subject). > > $query = "select t1.id as id , t1.subject , t2.id as target from mytable as > t1, mytable t2 where t2.id=30 having t1.id > target -16 and t1.id < target + > 16"; > $result = mysql_query($query , $db); > > That will return a 31 row result with records from 15 to 45 with a result > table that looks something like this: > > | id | subject | target | > ------------------------------ > | 15 | Some Guy | 30 | > | 16 | Another D | 30 | > | 17 | Blah Blah | 30 | > ... > > You don't even have to know the target id, anything column name can be used > in the where part. Let's say you only knew the subject of the target row. > > "...mytable t2 where t2.subject=\"HI!\" having..." > > --- > > I'll try to explain how this works. The query sorta combines 2 selects in > one. When we say "...where t2.column=whatever..." it's just like saying > "select * from mytable where column=whatever". So now, any t2 columns that > are in the select part of the query (in the above case it was just [t2.id as > target]) get the value from the where clause. Then comes the having clause > which can only use columns defined in the select part (t1.id as id , > t1.subject , t2.id as target). target will be the id from the result of the > where clause, so you can now tell it to give you the t1 values from 15 > before target and 15 after target. > > That's probably a big mess, hopefully you get something out of it. > > Also, you can use * in the select part if you would like. > > select t1.* , t2.id as target from mytable as t1, mytable t2 where > t2.subject="whatever" having t1.id > target -16 and t1.id < target + 16 > > This would return those 31 rows will every column from your table. > > SL. Steve, Let me first of all say thanks for you help, it would have taken quite some time to write all of this out, unfortunately most of this has just gone whoosh right over my head. I sort of get it but most likely don't at all :-) Basically what I am trying to do is let a person type in a Name for example 'John Smith' or a Number '15' (which is the racers bib number) and the select would find his/her place in the race and display the results for lets say 15 above and 15 below his/her result. Place | Name | BibNumber places 85 -99 100 John Smith 15 places 101 - 115 John Donagher helped me out with the BETWEEN syntax for mysql. This seemed to work, fine but I had to do a select to find the place for the name or bib number then use the Place (what position the person came) column as the number for the between. I would be interested in trying your JOIN method as it looks as if it would be more efficient? Could you rework your example based on the above race results, as I might be able to understand better, all those t1 t2 AS TARGET have really done my head in eh :-) Regards, Joseph