SQL - строки не существуют, поскольку существуют

У меня 3 стола. Один со списком пользователей, один с элементами и один со связью между пользователями и элементами. Пользователь может иметь элемент (stat = 0/1) или дубликат (dup = 0/1). Если вы нажмете кнопку элемента или дубликата, он запустит запрос, который добавит или обновит строку.

Это выглядит так:

| user | item_id | stat | dup |
| ---- | ------- | ---- | --- |
| 17   |   1     |  1   |  1  |
| 5    |   2     |  1   |  0  |
| 8    |   1     |  0   |  1  |
| 9    |   4     |  1   |  0  |

Я сравниваю эти данные, у кого есть элемент, которого нет у другого, и наоборот, с этим запросом:

SELECT t2.user_id,
       GROUP_CONCAT(CASE WHEN t1.dup=1 AND t2.stat=0 THEN item_id END) `My Item List`,
       GROUP_CONCAT(CASE WHEN t2.dup=1 AND t1.stat=0 THEN item_id END) `Item List`
FROM table3 t1
LEFT JOIN table3 t2 USING (item_id)
WHERE t1.user_id = @current_user
  AND t2.user_id <> @current_user
GROUP BY t2.user_id

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

После запуска кода таблица должна выглядеть так, чтобы я мог видеть отношения, даже если у пользователя нет (stat=0) этого элемента:

| user | item_id | stat | dup |
| ---- | ------- | ---- | --- |
| 17   |   1     |  1   |  1  |
| 5    |   2     |  0   |  0  |
| 5    |   6     |  1   |  0  |
| 8    |   1     |  0   |  1  |
| 9    |   4     |  1   |  0  |
| 9    |   6     |  0   |  0  |
| 17   |   13    |  0   |  0  |

Как я могу интегрировать это в запрос выше?

Мои столы:

Таблица 1

CREATE TABLE table1(
    id NOT NULL AUTO_INCREMENT,
    user_name varchar(255),
);

Таблица 2

CREATE TABLE table2(
    id NOT NULL AUTO_INCREMENT,
    item_no varchar(255),
    item_name varchar(255),
    item_group varchar(255)
);

Таблица 3

CREATE TABLE table3 (
  id int NOT NULL AUTO_INCREMENT,
  user_id int NOT NULL,
  item_id int NOT NULL,
  stat tinyint NOT NULL,
  dup tinyint NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (user_id) REFERENCES table1(id),
  FOREIGN KEY (item_id) REFERENCES table2(id)
);

Спасибо!

Покажите нам образцы данных для всех таблиц, а также ожидаемый результат. (То есть минимальный воспроизводимый пример.)

jarlh 17.04.2023 13:15

Добавьте образцы данных как INSERT INTO. Покажите желаемый результат для ваших образцов данных.

Akina 17.04.2023 13:51

обновлен выходными данными, которые я хочу

gibrish_diodo 17.04.2023 14:01
Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для
Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для
В последние годы архитектура микросервисов приобрела популярность как способ построения масштабируемых и гибких приложений. Laravel , популярный PHP...
Как построить CRUD-приложение в Laravel
Как построить CRUD-приложение в Laravel
Laravel - это популярный PHP-фреймворк, который позволяет быстро и легко создавать веб-приложения. Одной из наиболее распространенных задач в...
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
В предыдущем посте мы создали функциональность вставки и чтения для нашей динамической СУБД. В этом посте мы собираемся реализовать функции обновления...
Документирование API с помощью Swagger на Springboot
Документирование API с помощью Swagger на Springboot
В предыдущей статье мы уже узнали, как создать Rest API с помощью Springboot и MySql .
Роли и разрешения пользователей без пакета Laravel 9
Роли и разрешения пользователей без пакета Laravel 9
Этот пост изначально был опубликован на techsolutionstuff.com .
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
В предыдущей статье мы завершили установку базы данных, для тех, кто не знает.
0
3
83
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Что, если вы просто возьмете все уникальные комбинации user и item_id, а затем сопоставите только имеющиеся комбинации, а остальные оставите нулевыми? Мы можем заменить ноль на 0.

Что-то вроде,

SELECT 
  all_combos.user, 
  all_combos.item_id, 
  COALESCE(T.stat, 0) AS stat, 
  COALESCE(T.dup) AS dup 
FROM 
  (
    SELECT 
      user, 
      item_id 
    FROM 
      (SELECT DISTINCT user FROM table3) u, 
      (SELECT DISTINCT item_id FROM table3) i
  ) AS all_combos 
  LEFT OUTER JOIN table3 T ON T.user = all_combos.user 
  AND T.item_id = all_combos.item_id

Если вы хотите создать комбинации пользователей и элементов, которых никогда не было в таблице 3, вы можете объединить их, изменив подзапросы для u и i.

SELECT 
  all_combos.user, 
  all_combos.item_id, 
  COALESCE(T.stat, 0) AS stat, 
  COALESCE(T.dup) AS dup 
FROM 
  (
    SELECT 
      user, 
      item_id 
    FROM 
      (SELECT DISTINCT id as user FROM table1) u, 
      (SELECT DISTINCT id as item_id FROM table2) i
  ) AS all_combos 
  LEFT OUTER JOIN table3 T ON T.user = all_combos.user 
  AND T.item_id = all_combos.item_id

он говорит, что столбец «пользователь» недействителен в «списке полей». как я могу это исправить?

gibrish_diodo 18.04.2023 16:38

Может быть, это user_id? Вам придется исправить мой SQL в соответствии с вашими данными - в вашем результате указано USER, но в таблице указан USERID, поэтому я точно не знаю, как вы получили USER

Josh 18.04.2023 17:23

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

gibrish_diodo 19.04.2023 13:58

@gibrish_diodo подзапросы u и i — это то, на чем вы хотите сосредоточиться. если вы измените каждого из них на уникальных пользователей из другой таблицы (не таблицы 3), вы можете напрямую получать элементы и пользователей независимо от того, существовали они в таблице 3 или нет.

Josh 19.04.2023 14:01

так? (SELECT DISTINCT id FROM table1) u, (SELECT DISTINCT id FROM table2) . если я сделаю это, это не сработает, потому что первичный ключ обеих таблиц - id, и я не могу отделить его от all_combos.id

gibrish_diodo 19.04.2023 15:20

@gibrish_diodo я отредактировал ответ выше, чтобы дать вам эту альтернативу. Вы просто можете использовать псевдоним для идентификатора, чтобы у all_combos не было 2 столбцов с одинаковыми именами. И если вы присвоите им то же имя, что и в таблице 3, вам даже не нужно менять синтаксис соединения.

Josh 19.04.2023 15:23

оно работает! Спасибо! я застрял на этом в течение нескольких месяцев. Спасибо! теперь мне нужно объединить эти результаты с моим другим запросом.

gibrish_diodo 19.04.2023 15:57

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

gibrish_diodo 20.04.2023 12:55

Конечно, поскольку мы уже присоединяемся к table3, вы можете добавить фильтр WHERE T.user IS NULL, и он покажет вам только созданные нами "новые" строки, которых еще не было в table3. Изучите LEFT OUTER JOIN, и вы поймете, как это работает.

Josh 20.04.2023 15:02

Другие вопросы по теме