Reputation: 7789
http://thedailywtf.com/Articles/The-Hot-Room.aspx
You see how at the bottom there're links to the next and previous articles ("Unprepared For Divide_By_Zero" and "A Completely Different Game")? How do I do that, but selecting the next and previous non-private articles? This works for selecting the next article:
SELECT * FROM articles WHERE id > ? AND private IS NULL
But I cannot find a way to select the previous article.
What is the proper/efficient way to do this, preferably in one query?
Upvotes: 5
Views: 2833
Reputation: 247
You can get away with subselects etc in your particular case, but if you need anything more complicated (for example: given an initial balance and a list of payments and chargebacks, calculate account balance at every point of time) you probably would want to write a stored procedure that uses SQL REPEAT/WHILE/LOOP clauses and allows use of variables and so on.
Upvotes: 2
Reputation: 5223
Or extending Jeremy's answer...
In one query
(SELECT * FROM articles WHERE id > ?
AND private IS NULL
ORDER BY id ASC LIMIT 1)
UNION
(SELECT * FROM articles WHERE id < ?
AND private IS NULL
ORDER BY id DESC LIMIT 1)
Upvotes: 9
Reputation: 3583
How about a nested select?
SELECT * FROM articles WHERE id IN (
SELECT id FROM articles WHERE id > ? AND private IS NULL ORDER BY id ASC LIMIT 1)
)
OR id IN (
SELECT id FROM articles WHERE id < ? AND private IS NULL ORDER BY id DESC LIMIT 1
);
Upvotes: 2
Reputation: 176803
Here's how I would do it:
-- next
SELECT * FROM articles WHERE id > ? AND private IS NULL ORDER BY id ASC LIMIT 1
-- previous
SELECT * FROM articles WHERE id < ? AND private IS NULL ORDER BY id DESC LIMIT 1
I'm not sure how to do it in one query. The only thing I can think of is possibly getting both the article you're displaying and the next article in one query, but that might be too confusing.
Upvotes: 5