December 01, 2020, 08:35:09 pm
News: If someone gives you crap then give them a Bad Star
Pages: [1]   Go Down
Author Topic: MySQL Query help needed (or any SQL that I can use to figure this out).  (Read 5352 times)
Jason Reed
Administrator
*

Stars: +4/-0
Offline Offline

Gender: Male
Posts: 4545

Pure Evil Administrator


WWW
« on: April 02, 2007, 07:38:23 pm »

Ok say I have a table with 100 records.

I want to display a particular record in the table and links to the previous two and the next two as well. Is there a way to do this with only one SQL Statement?
Logged

-- signature --

SimonM
Hero Member
*

Stars: +0/-0
Offline Offline

Posts: 617



WWW
« Reply #1 on: April 24, 2008, 09:16:04 am »

Is there anything that links a record to it's neighbours besides it's physical position in the table? Do they have a sequential ID number?

If the answer to both the above two questions is 'no', it's going to be tricky...
Logged

Jason Reed
Administrator
*

Stars: +4/-0
Offline Offline

Gender: Male
Posts: 4545

Pure Evil Administrator


WWW
« Reply #2 on: April 24, 2008, 09:45:46 am »

There is a sequential id number but then the problem is that the sequence could be broken because of a record being removed from the database.
Logged

-- signature --

Martin Wallace
Founders
*

Stars: +1/-0
Offline Offline

Posts: 3602


God is dead, I have surpassed him.


WWW
« Reply #3 on: April 24, 2008, 06:23:16 pm »

Create a stored procedure:

Code:
CREATE PROCEDURE FetchFour
@Id int
AS
BEGIN
SET NOCOUNT ON;

DECLARE @UpperLimit int;
DECLARE @LowerLimit int;

SELECT TOP 2 @UpperLimit = _YourIdField_ from _YourTable_ WHERE _YourIdField_ > @Id ORDER BY _YourIdField_ ASC;
SELECT TOP 2 @LowerLimit = _YourIdField_ from _YourTable_ WHERE _YourIdField_ < @Id ORDER BY _YourIdField_ DESC;

SELECT * from _YourTable_ WHERE _YourIdField_ BETWEEN @LowerLimit and @UpperLimit ORDER BY _YourIdField_ ASC;

END
GO

You can then call it with an id that you want to fetch and it will return the two records either side.

This is based on SQL Server - YMMV.
Logged

Every time god kills an angel, I masturbate.

Jason Reed
Administrator
*

Stars: +4/-0
Offline Offline

Gender: Male
Posts: 4545

Pure Evil Administrator


WWW
« Reply #4 on: April 24, 2008, 06:39:09 pm »

Hmm will need to try this with MySQL 5 which I do believe has Stored Procedures.
Logged

-- signature --

Martin Wallace
Founders
*

Stars: +1/-0
Offline Offline

Posts: 3602


God is dead, I have surpassed him.


WWW
« Reply #5 on: April 24, 2008, 07:23:29 pm »

It should work Jason, they only thing I am not sure about is the bit where it sets the upper and lower limits.  In SQL Server a recordset that returns multiple rows (in this case 2 rows) will insert the values from the last row into the variables, I am not sure if this is the same with MySQL.
Logged

Every time god kills an angel, I masturbate.

Pages: [1]   Go Up
Print
 
Jump to: