CODEWITHSUNDEEP
mysqlsql
William Boman
William Boman

Reputation: 2227

Select the next and previous id with certain arguments

I have a table that looks like this (with non-important columns omitted

`pictures` (
  `picture_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `picture_title` varchar(255) NOT NULL,
  `album_id` int(10) unsigned DEFAULT NULL,
  `authenticated` tinyint(4) DEFAULT '0',
  `approved` tinyint(4) DEFAULT '0'
)

What I want to do is to get the next and previous authenticated and approved picture_id. There's a catch though - that occurrence either has to have picture_id = album_id or album_id IS NULL.

Here's an image to easier describe what I want to achieve. https://i.sstatic.net/slkit.png

The columns marked in red is what I want to select, the one marked in green is the picture_id I have and wanna find the next/previous id of. Note that it skips picture_ids 2 < 9 because it has an album_id that isn't equal to its picture_id and it skips picture_id 11 because it's not authenticated/approved.

How do I achieve this? I'm clueless.

edit: reworded a bit

Upvotes: 0

Views: 44

Answers (1)

dognose
dognose

Reputation: 20899

You could use a simple union query, where you take the following into account:

  • The middle picture matches your id.
  • The prior picture matches your conditions PLUS its id is lower, BUT is the highest id, from the lower part of the table.
  • The next picture matches your conditons PLUS its id is higher, BUT is the lowest id, from theupper part of the table.

Check this (Note id 2 getting skipped, cause it does not match the "id=album or album=null" contraint.):

See this fiddle: http://sqlfiddle.com/#!2/42ce6/17

  (SELECT 
    * 
  FROM 
    pics
  WHERE
    id = 10
  ) -- target id image
UNION
  (
  SELECT 
    * 
  FROM 
    pics
  WHERE
    id < 10 AND
    authenticated = 1 AND
    approved = 1 AND
    (id = album OR ISNULL(album))
  ORDER BY id DESC LIMIT 0,1
  ) -- highest id of lower part
UNION
  (
  SELECT 
    * 
  FROM 
    pics
  WHERE
    id > 10 AND
    authenticated = 1 AND
    approved = 1 AND
    (id = album OR ISNULL(album))
  ORDER BY id ASC LIMIT 0,1
  ) -- loweste id of upper part
  ORDER BY id

Limiting the "upper" part and "lower" part of the table to 1 matches exactly the 2 entries you want to fetch.

Output:

ID  TITLE   ALBUM   AUTHENTICATED   APPROVED
1   My fav  1   1   1
10  test 2  (null)  1   1
12  Holiday pictures    (null)  1   1

Upvotes: 1

Related Questions