Выборка из 4 таблиц в одну результатирующую

Question

Имеется 4 таблицы, которые нужно соединить

1. Таблица PRIVATE_CHAT.
В данной таблице мне нужно сгруппировать все пары user_from и user_to и отсеять все лишнее кроме записей с максимальным id для каждой пары, а так же вывести count ко всем имеющимся записям каждой пары по условию check_text, view_notif, hidden = 0.

id	user_from	user_to	check_text	view_notif	hidden
1	user	user_two	1	1	0
2	user_two	user	0	0	0
3	user	user_two	0	0	0
4	user_three	user	1	1	0
5	user	user_three	1	1	0
6	user	user_three	1	0	1

Желаемый результат:

id	user_from	user_to	check_text	view_notif	hidden	COUNT
3	user	user_two	0	0	0	2
6	user	user_three	1	0	1	0

Далее из таблицы USERS по предыдущему результату необходимо выбрать значение обоих столбцов по условию (private_chat.user_from = users.user_login AND private_chat.user_from <> 'user') OR (private_chat.user_to = users.user_login AND private_chat.user_to <> 'user')

user_login	user_icon
user	img/user.svg
user_two	img/user_two.svg
user_three	img/user_three .svg

Желаемый результат:

id	user_from	user_to	check_text	view_notif	hidden	COUNT	user_login	user_icon
3	user	user_two	0	0	0	2	user_two	img/user_two.svg
6	user	user_three	1	0	1	0	user_three	img/user_three.svg

Из 3й таблицы PRIVATE_SETTING необходимо получить значение столбцов status, status_check по условию из пред. результата user_login = private_setting.user

status	status_check	user
1	0	user_two
1	1	user_three
1	1	user

Желаемый результат:

id	user_from	user_to	check_text	view_notif	hidden	COUNT	user_login	user_icon	status	status_check
3	user	user_two	0	0	0	2	user_two	img/user_two.svg	1	0
6	user	user_three	1	0	1	0	user_three	img/user_three.svg	1	1

Из 4й таблицы ACTIONS_PRIVATE необходимо получить значение столбца time_ban по условию из пред. результата user_login = actions_private.user_to AND actions_private.user_from = 'user'

user_from	user_to	time_ban
user	user_two	6
user	user_three	40

Итоговый желаемый результат выборки из 4х таблиц:

id	user_from	user_to	check_text	view_notif	hidden	COUNT	user_login	user_icon	status	status_check	time_ban
3	user	user_two	0	0	0	2	user_two	img/user_two.svg	1	0	6
6	user	user_three	1	0	1	0	user_three	img/user_three.svg	1	1	40

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

Answer 1

SELECT get_last.id,
       get_last.user_from,
       get_last.user_to,
       private_chat.check_text,
       private_chat.view_notif,
       private_chat.hidden,
       get_last.`count`,
       users.user_login,
       users.user_icon,
       private_setting.status,
       private_setting.status_check,
       actions_private.time_ban
FROM private_chat
JOIN (
  SELECT MAX(id) AS id,
         'user' AS user_from,
         CASE WHEN user_from = 'user'
              THEN user_to
              ELSE user_from
              END AS user_to,
         SUM((check_text, view_notif ,hidden) = (0, 0, 0)) AS `count`
  FROM private_chat
  WHERE 'user' IN (user_from, user_to)
  GROUP BY 2, 3
  ) get_last USING (id)
JOIN users ON get_last.user_to = users.user_login
JOIN private_setting ON get_last.user_to = private_setting.user
JOIN actions_private ON actions_private.user_to = get_last.user_to
                    AND actions_private.user_from = 'user'

fiddle

БЛОГ НА HUSL

Выборка из 4 таблиц в одну результатирующую

Имеется 4 таблицы, которые нужно соединить

Ответы (1 шт):