Как извлечь данные только из одной таблицы при использовании операции соединения (join)? 

Добрый день!

У меня есть две таблицы: 

- **servers_texts**:  
  | id | cat_id | id |  
  |----|--------|----|  
  | 8  | 10     | 1  |  
  | 9  | 5      | 2  |  
  | 10 | 4      | 3  |  
  | 11 | 3      | 4  |  

- **servers_world**:  
  | cat_id | typew |  
  |--------|-------|  
  | 10     | 1     |  
  | 10     | 2     |  
  | 10     | 1     |  
  | 11     | 2     |  

Таблица **servers_texts** содержит категории, а **servers_world** — элементы категорий, где `servers_world.cat_id` соответствует ID родительской категории из **servers_texts**.

Задача состоит в том, чтобы выводить только те элементы из **servers_texts**, у которых в **servers_world** есть потомки с определенным типом `typew`. 

Например, для категории "10" имеются несколько потомков в **servers_world** с типом 1, но мой запрос возвращает эту категорию несколько раз из-за того, что в **servers_world** есть два совпадения. Как можно исправить это поведение? 

Вот мой текущий SQL-запрос: 
<pre><code class="sql">SELECT `servers_texts`.*, `servers_world`.`typew` FROM `servers_texts` LEFT JOIN `servers_world` ON `servers_texts`.`id` = `servers_world`.`cat_id` WHERE `servers_texts`.`premod` = 1 AND `servers_texts`.`cat_id` = '10' AND `servers_world`.`typew` = '1' ORDER BY `vote` DESC, `servers_texts`.`id` DESC</code></pre>
  • 28 августа 2024 г. 19:14
Ответы на вопрос 2 
Для того чтобы извлечь данные из одной таблицы при использовании операции соединения (JOIN) и избежать дублирования, можно применить группировку и функцию агрегации. В вашем случае, дополнительно стоит использовать проекцию только нужных полей из таблицы `servers_texts`, что позволит избежать повторений.

Вы можете использовать конструкцию `DISTINCT`, чтобы выбрать уникальные записи из таблицы `servers_texts`. Вот исправленный запрос:

```sql
SELECT DISTINCT `servers_texts`.*
FROM `servers_texts`
LEFT JOIN `servers_world`
ON `servers_texts`.`id` = `servers_world`.`cat_id`
WHERE `servers_texts`.`premod` = 1 
AND `servers_texts`.`cat_id` = '10' 
AND `servers_world`.`typew` = '1'
ORDER BY `vote` DESC, `servers_texts`.`id` DESC;
```

**Пояснения:**
1. **DISTINCT:** Это ключевое слово позволяет выбрать только уникальные записи из результата запроса. Это особенно полезно, если есть дубли из-за соединения с таблицей `servers_world`.
  
2. **Проекция из одной таблицы:** Запрос выбирает только поля, находящиеся в таблице `servers_texts`, с помощью `SELECT DISTINCT servers_texts.*`.

Если вам нужны только определенные колонки из `servers_texts`, то укажите их явно вместо использования `*`:

```sql
SELECT DISTINCT `servers_texts`.`id`, `servers_texts`.`cat_id`, ... -- другие необходимые столбцы
FROM `servers_texts`
LEFT JOIN `servers_world`
ON `servers_texts`.`id` = `servers_world`.`cat_id`
WHERE `servers_texts`.`premod` = 1 
AND `servers_texts`.`cat_id` = '10' 
AND `servers_world`.`typew` = '1'
ORDER BY `vote` DESC, `servers_texts`.`id` DESC;
```

Эта конструкция позволит избежать дублирования результатов при соединении, если у вас есть несколько записей в `servers_world`, соответствующих одной категории в `servers_texts`.
У Вас 2 варианта, 
 1. использовать отфильтрованный список servers_world 
 2 использовать значение typew в контексте выборки ( так же с фильтром ) 
 1-й подход может, в свою очередь, иметь два подхода, в зависимости от того, какие фишки SQL поддерживает Ваша СУБД. 
 
 1. C(ommon) T(able) E(xpression): 
 
 WITH filtered_servers_world AS ( 
 SELECT DISTINCT cat_id, typew 
 FROM filtered_servers_world 
 ) 
 SELECT `servers_texts`.* 
 , `filtered_servers_world`.`typew` 
 FROM `servers_texts` 
 LEFT JOIN `filtered_servers_world` ON `servers_texts`.`id` = `filtered_servers_world`.`cat_id` 
 WHERE `servers_texts`.`premod` = 1 
 AND `servers_texts`.`cat_id` = ' 10' 
 AND `filtered_servers_world`.`typew` = ' 1' 
 ORDER BY `vote` DESC, `servers_texts`.`id` DESC 
 
 2. Sub-Query с алиасом: 
 SELECT `servers_texts`.* 
 , `filtered_servers_world`.`typew` 
 FROM `servers_texts` 
 LEFT JOIN  (SELECT DISTINCT cat_id 
 , typew 
 FROM `servers_world`) AS `filtered_servers_world` 
 ON `servers_texts`.`id` = `filtered_servers_world`.`cat_id` 
 WHERE `servers_texts`.`premod` = 1 
 AND `servers_texts`.`cat_id` = ' 10' 
 AND `filtered_servers_world`.`typew` = ' 1' 
 ORDER BY `vote` DESC, `servers_texts`.`id` DESC 
 
 3. inline используя контекст выборки данных, менее эффективно, но иногда работает лучше подходов 1, 2 в частности, когда СУБД распределенная, поскольку уменьшает количество раз данные копируются между порциями данных разделенных на разные машины 
 
 SELECT * 
 FROM ( 
 SELECT `servers_texts`.* 
 , (SELECT MAX(`servers_world`.`typew`) FROM `servers_world` WHERE `servers_world`.`cat_id` = `servers_texts` .`cat_id`) AS `typew` 
 FROM `servers_texts` 
 WHERE `servers_texts`.`premod` = 1 
 AND `servers_texts`.`cat_id` = ' 10' 
 ) AS `dt` 
 WHERE `typew` = '1' 
 AND  `premod` = 1 
 AND `cat_id` = ' 10' 
 ORDER BY `vote` DESC, `servers_texts`.`id` DESC 
 
 4 lateral join или outer apply 
 
 SELECT `servers_texts`.* 
 , `filtered_servers_world`.`typew` 
 FROM `servers_texts` 
 LEFT JOIN  LATERAL ( 
 SELECT `typew` 
 FROM `servers_world` 
 WHERE `servers_world`.`cat_id` =  `servers_texts`.`cat_id` 
 ORDER BY `typew` DESC 
 LIMIT 1 
 ) `filtered_servers_world`  ON TRUE 
 WHERE `servers_texts`.`premod` = 1 
 AND `servers_texts`.`cat_id` = ' 10' 
 AND `filtered_servers_world`.`typew` = ' 1' 
 ORDER BY `vote` DESC, `servers_texts`.`id` DESC 
 
 Заметьте, я поменял поле на котором servers_texts и servers_world установили родство. В Вашем оригинальном запросе поле servers_texts использует id в JOIN выражении, но глядя на поля предложенные в таблицах cat_id выглядит, как поле, которое больше подходит. 
 Возможно я не прав, пожалуйста проверьте. 
 В крайнем случае, пожалуйста верните оригинальное поле. 
 
 -- Удачи
Похожие вопросы
    Показать ещё