CODEWITHSUNDEEP
phpmysqlsql
phantomCoder
phantomCoder

Reputation: 1587

Combine multiple mysql queries to get result as a single row

This is my table : question

id   | name
-----+-----
 2   | name 1
 3   | name 2 
 4   | name 3
 5   | name 4
 10  | name 5 
 20  | name 6
 21  | name 7

I have an id say 5, I need to get the previous and next rows, so if id is 5 I need to get 4, 5 and 10

Now I am using these queries

select * from question where id = (select max(id) from question where id < 5);
select * from question where id = (select min(id) from question where id > 5);

What I am looking is for a single query (if its possible). Some thing like

SELECT xx AS prevId, id, yy AS nextId FROM questions WHERE .......

If there is not previous id or next id it should be 0 or -1

For example
id: 5
Return : 4, 5, 10

id:2
Return : -1, 2, 3

id: 21
Return 20, 21, -1

I know how to do this with multiple queries and with some if conditions in PHP, but I am looking for a pure mySQL approach if its possible.

Upvotes: 0

Views: 1285

Answers (2)

Whome
Whome

Reputation: 10400

Here are few examples, someone else can tell which one is more efficient.

Query 1

Select
  (select id from question where id<5 order by id desc limit 0,1) as prevId,
  id,
  (select id from question where id>5 order by id asc limit 0,1) as nextId
From question
Where id=5;

Query 2

Select
  (select max(id) from question where id<5) as prevId,
  id,
  (select min(id) from question where id>5) as nextId
From question 
Where id=5;

Ah, did not see -1 definition, needs some more hacking. Coalesce function takes 1..n arguments and returns first nonnull value.

Query 3

Select
  Coalesce( (select max(id) from question where id<21), -1) as prevId,
  id,
  Coalesce( (select min(id) from question where id>21), -1) as nextId
From question
Where id=21;

Upvotes: 1

Moeed Farooqui
Moeed Farooqui

Reputation: 3622

For next Record(ID):

SELECT * FROM `table_name` WHERE ID > $id ORDER BY ID LIMIT 1;

For prev Record(ID):

SELECT * FROM `table_name` WHERE ID < $id ORDER BY ID LIMIT 1;

Here $id is a variable in php which changes according to your desire

Upvotes: 0

Related Questions