Три интересные задачи на знание SQL

[zerkms]

на таких небольших задачах (а три таблицы — это очень небольшая задача) себе же дешевле переписать один раз код и перевести базу в пригодный для нормальной работы вид.

ps: чем обусловлено требование «только 1 запрос»? задачи жизненные — давайте и условия будут жизненные (уточню: в жизни не всегда «меньше запросов — лучше»)

[enartemy]

В реальности было намного больше условий, выборок и так далее. Приходилось еще считать количество коментраиев, определять рейтинг, тэги, имена пользователей создавших галерею или фоту… Вощем, было нехило, но дело не в этом. Тут главное — способ решения. Решать по разному можно, подзапросами, джойнами и так далее…

1 запрос — это значит что решается спокойно 1-м запросом. Просто надо додуматься «как». Впрочем, в этом случае думать-то особо не надо.

[zerkms]

HAVING без аггрегирующих функций и группировки (который я вижу в первом ответе, который почти верный) уже наводит о мыслях, что что-то делается не так. это к вопросу о «спокойно решается 1 запросом».

ps: вы не считаете, что спортивные задачи на умение составлять запросы прикольнее решать на нормальных структурах, а не на «структурах из жизни»? :-)

[linch]

для первой задачи:
SELECT
  gal.*,
  (
    SELECT img.id FROM photo_image as img WHERE img.g_id = gal.id
    ORDER BY img.is_main_foto DESC, img.ordi LIMIT 1
  ) as main_photo_id
FROM
  photo_category as cat
INNER JOIN
  photo_gallery as gal
  ON
  gal.c_id = cat.id
WHERE
  cat.id = $catId
  AND
  cat.is_published = 1
  AND
  gal.is_published = 1
HAVING
  main_photo_id IS NOT NULL
ORDER BY
  gal.ordi


[enartemy]

Хорошо, а если ни одна фота не отмечена главной что будет? Впрочем начало неплохое…

[linch]

Будет выбрана первая нескрытая в порядке сортировки.

[linch]

пардон, невнимателен. Предыдущий ответ неверен :)

[linch]

В подзапросе условие будет выглядеть так:
WHERE img.g_id = gal.id AND ( img.is_main_foto =1 OR img.is_published = 1)

[enartemy]

Теперь вроде верно.

[kingoleg]

Это уже не один запрос, ибо подзапрос — поже запрос

[outcoldman]

sql-ex.ru/ на хабре? :) Ох сколько я времени там убил… Затягивает то как

[TWINc]

Спасибо за ссылку

[alexius2]

На первую задачу у меня получился такой запрос:

SELECT gal . * , (
  SELECT id
  FROM photo_image im
  WHERE im.g_id = gal.id
  AND im.is_published = TRUE
  ORDER BY im.is_main_foto, im.ordi
  LIMIT 1
)im_id
FROM photo_gallery gal
WHERE gal.c_id = ?
AND gal.is_published = TRUE
HAVING im_id IS NOT NULL
ORDER BY gal.ordi

* This source code was highlighted with Source Code Highlighter.

Это если предполагать что выбранная категория опубликована, иначе ещё с таблицей категорий связывать надо.
P.S. Не помню, оптимизирует ли Mysql запросы вида ORDER… LIMIT 1 (исользует ли индексы), если нет, то возможно нужно переписать по-другому.

[enartemy]

Этого не нужно было предполагать. Категория может быть и не опубликована. Да, связь с таблицей категорий обязательна.

[alexius2]

Для второй задачи можно сделать
ORDER BY abs(?-ordi) LIMIT 2

Но это будет медленно работать…

[alexius2]

Хотя если в галерее в среднем фотографий немного, то будет быстро.

[nblxa]

Я тоже так думал, но проблема в том, что если передан id первой или последней фотки, то результат не будет удовлетворять условию.

[linch]

Для второго задания:
SELECT
    img.*
FROM
    photo_image as img
WHERE
    img.ordi > ( SELECT img2.ordi FROM photo_image as img2 WHERE img2.id = ? LIMIT 1)
    AND
    img.is_published = 1
    AND
    img.g_id = ?
LIMIT 1


[enartemy]

Неа. Если так — это очень просто, да, и думать не надо. Нужно чтоб выполнялись условия. То есть если данная фота фходит в галерею или категорию которые НЕ опубликованы, то результат должен быть нулевым.

[Setti]

> не очень любят использовать условия HAVING

Если не ошибаюсь, многие сталкиваются с условием HAVING не из-за большой любви к SQL, а потому что без него в некоторых обстоятельствах просто не обойтись.

[mobilz]

т.е. подзапросы разрешены, но запрос должен быть один? =) я, конечно, дилетант, но как mysql кеширует подобные запросы? Ведет ли кеш подзапросных таблиц?

[zerkms]

никак не кеширует. и практически не оптимизирует.

[DmitriyEntelis]

imho подзапросы зло.
на практике оно зачастую работает медленнее чем 2 отдельных запроса.

[pwlnw]

Вы так категоричны.
Только если подзапрос зависит от внешнего (коррелирующий).

[kingoleg]

100%. Для подзапросов MySQL часто забивает на индексы, даже если индекс тривиально можно построить

[Joka]

а не быстрее ли будет выполнить допустим 3 запроса по индексу из каждой таблицы чем один запрос сложной структуры? поиграться конечно с заковырками можно, а вот надо ли?

как говорит макконел — если я слышу словосочетание «хитрый код», то сразу думаю что код плохой ибо код должен быть простым и легок в понимании

[zerkms]

угу, указал это же в первом комментарии.

ps: у макконнелла куда более «хитрая» фамилия :-)

[gnomeby]

Захожите на sql-ex, там вас научат даже регулярки стандартными средствами SQL-синтаксиса решать. Реально отбивает охоту решать все SQL-задачи на годы.

[nblxa]

Сейчас я побрюзжу про отсутствие FOREIGN KEY. Все, побрюзжал, начинаю решать.

[nblxa]

Второе (без юниона, но жесть, т.к. как я понял, надо выбрать и следующую, и предыдущую):

select
( select i.id
 from photo_image i
 where i.is_published = 1
  and i.g_id = :g_id
  and i.ordi < (select x.ordi from photo_image x where x.id = :id)
 limit 1
) as prv,
( select i.id
 from photo_image i
 where i.is_published = 1
  and i.g_id = :g_id
  and i.ordi > (select x.ordi from photo_image x where x.id = :id)
 limit 1
) as nxt;

* This source code was highlighted with Source Code Highlighter.

В оракле гораздо проще, но по условию нельзя :-(

select
 lag(i.id, 1) over (order by ordi) prv,
 lead(i.id, 1) over (order by ordi) nxt
from photo_image i
where i.is_pubilshed = 1
 and i.g_id = :g_id
 and i.id = :id;

* This source code was highlighted with Source Code Highlighter.

[CAH4A]

SELECT *
FROM
    image LEFT JOIN folder USING (folder_id)
         LEFT JOIN cathegory USING (cathegory_id)
WHERE
    image.folder_id = (SELECT folder_id FROM images WHERE image_id=@image_id) AND
    folder.is_published = 1 AND
    cathegory.is_published = 1
ORDER BY
    ABS(ordi - (SELECT ordi FROM images WHERE image_id=@image_id))
LIMIT 2;

* This source code was highlighted with Source Code Highlighter.

Или я что-то не учёл? :)

[nblxa]

По поводу сортировки по модулю разницы: я выше уже писал, что «если передан id первой или последней фотки, то результат не будет удовлетворять условию».
Где image.is_pulbished = 1?
LEFT JOIN здесь не нужен, здесь достаточно INNER JOIN.

[CAH4A]

SELECT *
FROM
    image JOIN folder USING (folder_id)
         JOIN cathegory USING (cathegory_id)
WHERE
    image.folder_id = (SELECT (@folder_id:=folder_id) FROM images WHERE image_id=@image_id) AND
    image.is_published = 1 AND
    folder.is_published = 1 AND
    cathegory.is_published = 1
ORDER BY
    ABS(ordi - (SELECT (@ord := ordi) FROM images WHERE image_id=@image_id))
LIMIT
SIGN((SELECT count(ordi) FROM images WHERE folder_id=@folder_id AND ordi>@ord))
+
SIGN((SELECT count(ordi) FROM images WHERE folder_id=@folder_id AND ordi<@ord));

* This source code was highlighted with Source Code Highlighter.

((( Но mysql вычислять LIMIT не разрешает. Тогда можно включить так:

SELECT images.*,
    (SELECT count(ordi) FROM images WHERE folder_id=(SELECT (@folder_id:=folder_id) FROM images WHERE image_id=@image_id) AND ordi>(SELECT (@ord := ordi) FROM images WHERE image_id=@image_id)) as next,
    (SELECT count(ordi) FROM images WHERE folder_id=(SELECT (@folder_id:=folder_id) FROM images WHERE image_id=@image_id) AND ordi<(SELECT (@ord := ordi) FROM images WHERE image_id=@image_id)) as prev
FROM
    image JOIN folder USING (folder_id)
         JOIN cathegory USING (cathegory_id)

WHERE
    image.folder_id = @folder_id AND
    image.is_published = 1 AND
    folder.is_published = 1 AND
    cathegory.is_published = 1
ORDER BY
    ABS(images.ordi - @ord)
LIMIT 2;

* This source code was highlighted with Source Code Highlighter.

и парсить пыхой.

[CAH4A]

Дурня.
Так лучше:

SELECT images.*,
    (images.ordi - (SELECT (@ord:=ordi) FROM images WHERE image_id=@image_id)) as destination
FROM
    image JOIN folder USING (folder_id)
         JOIN cathegory USING (cathegory_id)

WHERE
    image.folder_id = (SELECT (@folder_id:=folder_id) FROM images WHERE image_id=@image_id) AND
    image.is_published = 1 AND
    folder.is_published = 1 AND
    cathegory.is_published = 1
ORDER BY
    ABS(ordi - @ord)
LIMIT 2;

* This source code was highlighted with Source Code Highlighter.

[nblxa]

Я от особенностей MySQL далек довольно, можно объяснить, что значит «SELECT (@ord:=ordi) FROM images ...» (интересует то, что в скобках)? А то я как-то сходу не смог понять.

[enartemy]

Использование локальной переменной. Без этого 3-ю задачу решить очень трудно (точнее я так и не смог прдумать как это сделать).

[MARDEN]

Создание переменной @ord, которой присваивается значение столбца ordi.

[CAH4A]

SELECT (@folder_id:=folder_id) FROM images LIMIT 1
обозначает, что мы возвращаем folder_id, но так же записываем его в переменную @folder_id.

Есть ещё конструкция
SELECT folder_id FROM images LIMIT 1 INTO @folder_id

Здесь значение просто запришеться в @folder_id, но не будет результатом выборки.

[nblxa]

Первое (громоздко вышло, выше было решение, которое мне самому больше понравилось):

select g.id, g.title, i.id, i.title
from photo_category c
 inner join photo_gallery g
  on g.c_id = c.id
 inner join photo_image i
  on i.g_id = g.id
 inner join photo_image i_1
  on i_1.g_id = i.g_id
where (i.is_main_foto = 1
  or i.is_published = 1)
  and i_1.is_main_foto = 0
  and i_1.is_published = 1
  and g.is_published = 1
  and c.is_published = 1
  and c.id = :id
group by g.id, g.title, i.id, i.title
having count(i.id) > 1
  or (count(i.id) <= 1
  and count(i_1.id) > 0)
order by
 g.ordi,
 i.ordi;

* This source code was highlighted with Source Code Highlighter.

[enartemy]

Скажите, а зачем «group by g.id, g.title, i.id, i.title» и туча условий having?..

[pharod]

третья, остальные не интересно

SELECT
    c_id
    , g_id
    , (
        SELECT id
        FROM photo_image p 
        WHERE
            is_published = 1
            AND p.g_id = yy.g_id
        ORDER BY is_main_foto DESC, ordi
        LIMIT 1
    ) p_id
FROM
    (
        SELECT
            c.id c_id
            , g.id g_id
            , IF(
                @typex = g.c_id
                , @rownum := @rownum + 1
                , @rownum := 1 + LEAST(0, @typex := g.c_id)
            ) AS rown
        FROM
            photo_category AS c
            INNER JOIN photo_gallery AS g ON g.c_id = c.id
            , (SELECT @rownum := 1, @typex := '_') zz
        WHERE
             c.is_published = 1
             AND g.is_published = 1
        ORDER BY c.ordi, g.ordi DESC
    ) yy
WHERE rown <= 5

* This source code was highlighted with Source Code Highlighter.

[alex14n]

как минимум не выполняется условие (4) — не выберется фотка с is_main_foto=1, is_published = 0

[pharod]

согласен, просмотрел
Выше уже написали вариант: WHERE (is_main_foto = 1 OR is_published = 1)…

[enartemy]

Запрос не учитывает возможную пустоту галереи, + в этом случае WHERE (is_main_foto = 1 OR is_published = 1) ничего не даст. Если не считать этого, то ваше — единственное предложенное верное решение 3-ей задачи.

[pharod]

пустые галереи лечатся, например, через:
GROUP BY 1, 2 HAVING p_id
в самом конце запроса, но это уже мелочи

[alex14n]

первая:

select g.id, ifnull(
(select id from photo_image i where i.i.g_id=g.id and is_main_foto=1),
(select id from photo_image i where i.i.g_id=g.id and is_published=1 limit 1)
) as photo_id
from photo_gallery g
where g.c_id = ...
and g.is_published = 1
and exists(select 1 from photo_image i where i.g_id=g.id and i.is_published=1)
order by g.ordi

комментарий: в photo_image проиндексировать g_id
или хотя бы одну из пар (g_id, is_published) (g_id, is_main_foto)

вторая:

select (select i2.id from photo_image i2
where i1.g_id = i2.g_id and i2.ordi > i1.ordi
and (i2.is_published = 1 or i2.is_main_foto = 1)
order by i2.ordi asc limit 1) as next_id,
(select i2.id from photo_image i2
where i1.g_id = i2.g_id and i2.ordi < i1.ordi
and (i2.is_published = 1 or i2.is_main_foto = 1)
order by i2.ordi desc limit 1) as prev_id
from photo_image i1 where i1.id = ...

комментарий: проиндексировать (g_id, ordi)

третья:

select c.id as c_id, g.id as g_id, ifnull(
(select id from photo_image i where i.i.g_id=g.id and is_main_foto=1),
(select id from photo_image i where i.i.g_id=g.id and is_published=1 limit 1)
) as photo_id
from photo_category c
join photo_gallery g on g.c_id = c.id
where c.is_published=1 and g.is_published=1
and exists(select 1 from photo_image i where i.g_id=g.id and i.is_published=1)
and g.id in (select g2.id from photo_gallery g2 where g2.c_id = c.id order by ordi desc limit ...)
order by c.ordi desc, g.ordi desc

комментарий: поможет составной индекс (c_id asc, ordi desc) у photo_gallery

[zerkms]

заодно приложите эксплейны, если вас не затруднит?

>> комментарий: поможет составной индекс (c_id asc, ordi desc) у photo_gallery
с каких пор mysql [b]умеет[/b] в составных индексах задавать порядок?

[alex14n]

эксплейны только представляю в голове, на реальной базе не пробовал. для 3й задачи возможно лучше:

select c.id as c_id, g.id as g_id, ifnull(i.id,
(select id from photo_image i where i.i.g_id=g.id and is_published=1 limit 1)
) as photo_id
from (select id, (select min(ordi) as min_ordi
from (select ordi from photo_gallery g
where g.c_id = c.id and g.is_published=1
and exists (select 1 from photo_image i where i.g_id=g.id and i.is_published=1)
order by ordi desc limit ...))
from photo_category c where c.is_published=1) c
join join photo_gallery g on g.c_id = c.id and g.is_published=1 and g.ordi >= min_ordi
and exists (select 1 from photo_image i where i.g_id=g.id and i.is_published=1)
left join photo_image i on i.g_id=g.id and is_main_foto=1
order by c.ordi desc, g.ordi desc

[enartemy]

запрос не работает…

[alex14n]

да, виноват, писал из головы, запускать даже не пробовал.
внутринний подзапрос, выбирающий ограничение на ordi должен быть таким:

select id, (select ordi from photo_gallery g
where g.c_id = c.id and g.is_published=1
and exists (select 1 from photo_image i where i.g_id=g.id and i.is_published=1)
order by ordi desc limit 5, 1) x
from photo_category c where c.is_published=1

сейчас попробую проверить что не только ошибок нет, но и что на данных результат правильный

[alex14n]

select c.id as c_id, g.id as g_id, ifnull(i2.id,
(select id from photo_image i3 where i3.g_id=g.id and is_published=1 limit 1)
) as photo_id
from (
select c.id, c.ordi, (select ordi from photo_gallery g
where g.c_id = c.id and g.is_published=1
and exists (select 1 from photo_image i where i.g_id=g.id and i.is_published=1)
order by ordi desc limit ..., 1) min_ordi
from photo_category c where c.is_published=1
) c
join photo_gallery g on g.c_id = c.id and g.is_published=1
and (g.ordi > min_ordi or min_ordi is null)
and exists (select 1 from photo_image i where i.g_id=g.id and i.is_published=1)
left join photo_image i2 on i2.g_id=g.id and i2.is_main_foto=1
order by c.ordi desc, g.ordi desc

вроде работает, в многоточие надо поставить нужное ограничение на число групп
P.S. внутренний конечно, self-fix
P.P.S. всю ночь без интернета просидел

[feodal]

2-ая

SELECT image.*

FROM photo_image as image, photo_gallery as gal, photo_category as cat

where image.g_id in (select gal2.id from photo_gallery as gal2 where gal2.id=gal.id)
and gal.id in (select cat2.id from photo_category as cat2 where cat2.id=cat.id)
and cat.is_published=1 and gal.is_published=1 and image.is_published=1
and image.ordi > (select min(image2.ordi) from photo_image as image2 where image2.id=0)

order by image.ordi

limit 1;

[nblxa]

А вообще, по-хорошему, если сделать вместо is_main_foto у фотографии main_photo_id и first_photo_id у галереи, то огромной части всего этого SQL-геморроя можно было бы избежать.

[atari]

В первой задаче я бы презрел нормализацию таблиц и у галереи появилось бы поле «тайтловая фото».
Во второй —
SELECT p.id FROM photo_image AS o LEFT JOIN photo_image AS p ON p.g_id=o.g_id AND p.ordi > o.ordi AND is_published = 1 WHERE o.id =? ORDER BY p.ordi LIMIT 1;

[nblxa]

Не так давно мне попалась одна интересная и невероятно сложная задача (pdf) по SQL:

Есть 20-гранная игральная кость с числами на ней (числа не от 1 до 20, они случайны и не повторяются).
Есть таблица, содержащая все числа с кости и вероятности их выпадения.

Надо написать SQL-запрос, который выдаст вероятности выпадения каждой из возможных сумм чисел при N бросках (N — переменная).

[zerkms]

N раз перемножить декартово таблицу саму на себя (inner join), сложить, каждую сумму разделить на 20^n. на выходе — получим искомые p.

[zerkms]

пардон:
1. забыл группировку по значению суммы
2. делить не сумму, а COUNT(*)

[nblxa]

Так-то оно так, но это решение только для конкретного N. Есть как минимум 10 универсальных решений.

[nblxa]

Упс, то есть 9. Я сам из них додумался бы только до одного.

[nblxa]

Так не выйдет. N — переменная, которая передается в запрос, то есть запрос должен быть универсальным. Ограничений на использование возможностей БД нет.

Правда есть косяк, я тут посмотрел получше, по условию задачи (pdf-файлик), там не говорится про MySQL:
«Contestants may use any database technology at their disposal, but the submitted solutions should be compatible with at least one of the following database tech-nologies: Oracle 11g for Windows, SQL Server 2008, and DB2 9.5 for Windows».

То есть решение должно может быть написано для любой СУБД, но быть совместимым с Oracle 11g, SQL Server 2008 или DB2 9.5.

[alex14n]

тоже одним запросом? :)

[nblxa]

Да, одним запросом, но ограничения на использование возможностей СУБД состоит в том, что решение должно быть совместимым с Oracle 11g, SQL Server 2008 или DB2 9.5.

[leoneed]

Ща хабр решит, что сегодня массово пытаються пропихнуть иньекции в него))))

[Goodkat]

у меня тоже была иерархическая структура (фуры, палеты, коробки), и при неправильно статусе где-то выше нужно было исключать всё, что ниже

я на триггерах/процедурах сделал

если не хочется, чтобы снятие галочки с самой верхней папки автоматом сбрасывало галки со всех вложенных, то да, добавить второе поле is_parent_published, которое устанавливается триггером сверху.
и вывод делается только в случае если is_published = 1 and is_parent_published = 1

я так понимаю, изменения поля is_published происходят не очень часто, так что триггеры не будут особо напрягать базу, зато запросы на самом нижнем уровне делаются гораздо чаще, и джойны и вложенные запросы будут медленней работать.

да и по is_published индекс бы не помешал, ведь выборка ведётся по этому полю, не?

[Lord_Daedra]

> ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

MySQL лучше не грузить сложными запросами — два-три простых выполнятся быстрее, поэтому условие минимизации числа запросов не совсем понятно…

Красиво в данном случае — это не когда 1 запрос вместо 10, красиво — это когда работает максимально быстро…

[LeValdemar]

Зачача первая:

select pg.id as PG_ID, pim.*, count(pim.id) as PI_IMG_NUMBER 
	from photo_gallery pg inner join photo_image pim on (pg.id=pim.g_id) 
	where pg.c_id=CATEGORY_ID and pg.is_published=1
	group by pg.id 
	having (PI_IMG_NUMBER > 0)
	order by pg.ordi, pim.is_main_foto DESC, pim.is_published DESC, pim.ordi 

Задача вторая:

select pim.* 
	from photo_image pim cross join photo_image pim2 
		on (pim.g_id=pim2.g_id)
	where pim2.ordi > pim.ordi and pim2.is_published=1 and pim.id=IMAGE_ID
	limit 0, 1

Для этого запроса можно уменьшить скорость выполнения, если заранее известна галерея

select pim.* 
	from photo_image pim cross join photo_image pim2 
		on (pim.g_id=pim2.g_id and pim.g_id=GALLERY_ID)
	where pim2.ordi > pim.ordi and pim2.is_published=1 and pim.id=IMAGE_ID
	limit 0, 1

Для третьей не совсем понятно что реально надо сделать. Либо вывести некоторое количество картинок по категориям, либо вывести некоторое количество галерей по категориям, в которых расположены картинки, и соотвественно показать количество картинок для каждой категории.

[LeValdemar]

Подправил первую задачу, так как inner join даст возможность не обращать внимания на существования внутри таблицы с картинками какой-либо категории (то есть выберутся все, которые существуют только в таблице с галереями). А про вариант, когда в галерее могут быть исключительно все неотображаемые картинки недосмотрел. Итоговый:

select pg.id as PG_ID, pig.*
	from photo_gallery pg inner join photo_image pim on (pg.id=pim.g_id) 
	where pg.c_id='1' and pg.is_published=1 and (pim.is_published=1 or pim.is_main_foto=1)
	group by pg.id 
	order by pg.ordi, pim.is_main_foto DESC, pim.is_published DESC, pim.ordi

[enartemy]

Оригинально, только категрия может быть и неопубликована, + я долго не мог понять что имелось ввиду под pig.*

[LeValdemar]

pig.* => pim.* ;-)

В запросе было pig, переносил — забыл поменять ;-)

ЗЫ. более интересна реакция на решение второй задачи

[321]

Во второй задачи ошибочка, наверное надо (pim.id!=IMAGE_ID), а то смысла нет :)

Получаем:
select pim.*
from photo_image pim cross join photo_image pim2
on (pim.g_id=pim2.g_id and pim.g_id=1)
where pim2.ordi < pim.ordi and pim2.is_published=1 and pim.id!=2
limit 0, 1

[LeValdemar]

Если по условию задачи: все элементы ordi разные для одной галереи.
А если с чистой логики: не может один и тот же элемент (с одним и тем же id) иметь два разных значения ordi.

Но ошибка действительно есть ;-) двойку потерял. Да и плюс, элементов в тестовую таблицу накидал парочку всего и просмотрел, что порядок ordi может не соблюдаться, то есть, элемент мы конечно найдём, но не обязательно тот, который действительно искали.

Этот запрос на следущий элемент:

select pim2.* 
	from photo_image pim cross join photo_image pim2 
		on (pim.g_id=pim2.g_id and pim.g_id=GALLERY_ID)
	where pim2.ordi > pim.ordi and pim2.is_published=1 and pim.id=IMAGE_ID
	order by pim2.ordi ASC
	limit 0, 1

Этот запрос на предыдущий:

select pim2.* 
	from photo_image pim cross join photo_image pim2 
		on (pim.g_id=pim2.g_id and pim.g_id=GALLERY_ID)
	where pim2.ordi < pim.ordi and pim2.is_published=1 and pim.id=IMAGE_ID
	order by pim2.ordi DESC
	limit 0, 1

Кроме того, если уже хочется, чтобы было точно известно, что мы не должны получить тот же id фотографии (хотя и предыдущих запросов хватит), то можно добавить

where pim2.ordi < pim.ordi and pim2.is_published=1 and pim.id=IMAGE_ID and NOT(pim.id=pim2.id)

[caezar]

И все таки, с чем связано неизменение структуры?
>да это было бы и не интересно
Не интерестно работать со структурой, которая очевидно не наилучшая. В которой приходиться городить костыли вместо простых решений

[caezar]

если говорить о первой задаче — то main_foto_id решил бы ее намного красивее и быстрее

[bullgare]

первая задача
SELECT g.id, i.id FROM photo_gallery AS g
LEFT JOIN photo_category AS c ON c.id = g.c_id
LEFT JOIN
(SELECT id, g_id FROM photo_image ORDER BY is_main_foto DESC LIMIT 1) AS i
ON i.g_id = g.id
WHERE c.id = 1 AND i.id IS NOT NULL

вторая задача
SELECT id FROM photo_image AS i
WHERE ordi > (SELECT ordi FROM photo_image WHERE id = 2)
AND is_published > 0
LIMIT 1
третью просто некогда, пардон

[mente]

Давайте уточним — подзапрос считается за запрос?

[MARDEN]

Кстати, а где обещанное авторское решение задач?

[enartemy]

Сижу, готовлю сейчас. :-)

[bikutoru]

Навскидку, вторая задача запросом без UNION решается так:

SELECT i2.*
FROM photo_image i1
INNER JOIN photo_image i2 ON (i2.is_published=1) AND (i1.g_id=i2.g_id) AND (i2.ordi > i1.ordi)
WHERE i1.id=?
ORDER BY i2.ordi
LIMIT 1

[bolsh]

1)
SELECT *, (
SELECT id
FROM photo_image AS pi
WHERE pi.g_id = pg.id
AND (
is_main_foto =1
OR is_main_foto =0
)
ORDER BY is_main_foto DESC
LIMIT 1
) AS Image_id
FROM `photo_gallery` AS pg
WHERE `c_id` =2
AND `is_published` =1
HAVING Image_id IS NOT NULL
ORDER BY `ordi` DESC
2) Для случая, когда известно, что у фотки точно есть слейдующая и предыдушая придумал такое:
SELECT pi2.id, pi2.ordi, pi3.id, pi3.ordi
FROM `photo_image` AS pi
INNER JOIN `photo_image` AS pi2 ON pi.g_id = pi2.g_id
INNER JOIN `photo_image` AS pi3 ON pi2.g_id = pi3.g_id
WHERE pi.`id` =167
AND pi.`is_published` =1
AND pi2.`is_published` =1
AND pi3.`is_published` =1
AND pi2.id != pi.id
AND pi3.id != pi.id
AND pi3.ordi > pi.ordi
AND pi2.ordi < pi.ordi
ORDER BY `pi2`.`id` ASC, pi3.id DESC

Если фотка первая или вторая, то можно дополнительный запрос использовать на проверку граничных значений.

3) Тут явно одним запросом не обойтись, пока идей нет. Хотелось бы посмотреть вариант автора.

[bolsh]

посмотрел решения, довольно интересно, спасибо