Объединение трех таблиц MySQL для системы избранного пользователя

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

Во-первых, вот моя таблица fruitsinfo:

        id     |   color   |    rating
      apples        red          8.4
      oranges      orange        9.1
   blueberries      blue         8.8
    pineapple      yellow        7.9
      peaches      orange        8.3

Затем есть таблица currentfruit, в которой перечислены только сезонные фрукты и указана их текущая рыночная цена (эти данные до смешного плохие, но потерпите меня):

        id     |  price   |   months left in season
    apples         1.25            6
    avocados       1.42            4
    pears          1.24            3
    oranges        1.75            5
    blueberries    2.20            4

Наконец, таблица userfavorites, которая содержит идентификатор пользователя и идентификатор плода:

      userid  |   fruitid
        5          apples
        5          peaches
        5           pears

Мы просто будем работать с пользователем с userid = '5'. Здесь следует отметить пару вещей: не все записи в currentfruits находятся в fruitsinfo и не все записи в userfavorites находятся в currentfruits.

Когда пользователи обычно заходят на сайт без сохранения избранного, они просто видят, что currentfruits осталось вместе с fruitsinfo и упорядочено по цене, например:

   id     |  price   |   months left in season  | color  | rating 
blueberries   2.20                  4              blue    8.8
  oranges     1.75                  5             orange   9.1
  avocados    1.42                  4              null    null
  apples      1.25                  6              red     8.4
   pears      1.24                  3              null    null

Теперь я хочу, чтобы была проверка, чтобы увидеть, есть ли какие-либо из любимых фруктов пользователя в таблице currentfruits, а затем перечислить сначала эти результаты (упорядоченные по цене), а затем остальные текущие фрукты (упорядоченные по цене). У нашего пользователя есть яблоки, груши и персики в качестве фаворитов, но только яблоки и груши есть в currentfruits, поэтому теперь таблица должна выглядеть так:

   id     |  price   |   months left in season  | color  | rating 
  apples      1.25                  6              red     8.4
   pears      1.24                  3              null    null
blueberries   2.20                  4              blue    8.8
  oranges     1.75                  5             orange   9.1
  avocados    1.42                  4              null    null

Моя первоначальная мысль заключалась в том, чтобы сделать что-то вроде этого:

SELECT * 
FROM userfavorites
JOIN currentfruits ON userfavorites.fruitid = currentfruits.id
JOIN fruitsinfo ON currentfruits.id = fruitsinfo.id
ORDER BY currentfruits.price DESC

UNION

SELECT *
FROM currentfruits
LEFT JOIN fruitsinfo ON currentfruits.id = fruitsinfo.id
ORDER BY currentfruits.price DESC

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

Может ли кто-нибудь сказать мне, как я могу сделать запрос, который выполняет то, что я хочу сделать? Спасибо.


mysql sql favorites join select
person Kris    schedule 28.04.2013    source источник

Ответы (1)


arrow_upward
3
arrow_downward

Вам не нужно использовать UNION. Пытаться:

select c.id, c.price, c.`months left in season`, i.color, i.rating 
from currentfruit c
left join fruitsinfo i on c.id = i.id
left join userfavorites f on c.id = f.id and f.userid = 5
order by case when f.id is not null then 0 else 1 end, c.price desc
person Community    schedule 28.04.2013