Sql inner join 3 таблицы примеры: mysql - Как связать 3 таблицы в SQL запросе?

Содержание

sql — Выборка из нескольких таблиц inner join

Вопрос задан 4 года 4 месяца назад

Изменён 4 года 4 месяца назад

Просмотрен 10k раз

Имеется структура БД следующего вида:

Мне необходимо сделать выборку из таблицы Drivers(date, pack, directory) и Usable(system). При написании запроса у меня(скорее всего) где-то декартово произведение, ибо выдаётся большое количество строк — дубликатов. Использование distinct проблему решает, удаляя дубликаты, и я получаю нужный результат, однако я и сам понимаю, что это жутки костыль и так делать не нужно. Ниже приведён текст моего запроса, посмотрите, может быть я делаю очевидную ошибку:

select Drivers.pack Drivers.directory, Drivers.[date], Usable.[system]
from Sections inner join Drivers
on
(
    Drivers.  id in 
   (
        select driverId from Sections where Sections.id in 
       (
           select sectionId from Usable where deviceId in 
           (
                select id from Devices where deviceId like "%VEN_14F1%" and deviceId like "%DEV_8880%"
           )
        )
    )
    and Drivers.id = Sections.driverId
)
inner join Usable
on
(
     Usable.[system] = "6.1x64"
     and
     Usable.sectionId = Sections.id
)
order by(Drivers.[date]) desc;

Подскажите решение. P.S.: код отформатировал, надеюсь, так будет удобнее читать.

UPD:

SELECT
    Drivers.date,
    Drivers.pack,
    Drivers.directory,
    Usable.system
FROM
    Drivers
INNER JOIN Sections ON Drivers.id = Sections.driverId
INNER JOIN Usable ON Sections.id = Usable.sectionId
inner join Devices on Usable.deviceId = Devices.id
WHERE Devices.deviceId LIKE "%VEN_14F1%" AND Devices.deviceId LIKE "%DEV_8880%" AND Usable.system = '6.1x64'
order by(Drivers.[date]) desc;

sql
sqlite

Попробуйте:

SELECT
    Drivers. date,
    Drivers.pack,
    Drivers.directory,
    Usable.system
FROM
    Drivers
INNER JOIN Sections ON Drivers.id = Sections.driverId
INNER JOIN Usable ON Sections.id = Usable.sectionId
WHERE
    Usable.deviceId LIKE "%VEN_14F1%" AND Usable.deviceId LIKE "%DEV_8880%" AND Usable.system = '6.1x64'

Использование группировки решает проблему:

group by (Drivers.directory)

Зарегистрируйтесь или войдите

Регистрация через Google

Регистрация через Facebook

Регистрация через почту

Отправить без регистрации

Почта

Необходима, но никому не показывается

Отправить без регистрации

Почта

Необходима, но никому не показывается

Нажимая на кнопку «Отправить ответ», вы соглашаетесь с нашими пользовательским соглашением, политикой конфиденциальности и политикой о куки

sql — Объединить 3 таблицы в сложном запросе

Привет всем.

У меня есть три таблицы: games, active\_user_games, balance.

games
+------+---------+
|  id  |  title  |
+------+---------+
|  1   | my-game |
+------+---------+
|  2   | the are |
+----------------+
active\_user_game
+------+-----------+--------------+
|  id  |  user_id  |   game_id    | 
+------+-----------+--------------+
|  1   | 1         |     1        |
+------+-----------+--------------+
|  2   | 2         |     1        |
+------------------+--------------+
|  3   | 3         |     2        |
+------+-----------+--------------+
|  4   | 4         |     2        |
+------------------+--------------+
balance
+------+-----------+--------------+------------+
|  id  |  user_id  |   item_id    |   summ     |
+------+-----------+--------------+------------+
|  1   | 1         |     1        |   1000     |
+------+-----------+--------------+------------+
|  2   | 2         |     1        |   1000     |
+------------------+--------------+------------+
|  3   | 3         |     2        |   2000     |
+------+-----------+--------------+------------+
|  4   | 4         |     2        |   2000     |
+------------------+--------------+------------+

Мне нужно вывести все записи из таблицы games и добавить 2 столбца данных к каждой записи, которые вычисляются по данным из двух остальных таблиц. А именно мне нужно вывести количество участников из таблицы active\_user_game к конкретной игре функцией count() и сумму платежей этих участников из таблицы balance функцией sum(balance.summ).
Я делаю такой запрос:

SELECT g.id, g.title, au.game_id, au.id, b.id, b.user_id, COUNT(au.game_id) AS users,     SUM(b.summ) AS bank
FROM games AS g
  JOIN active_user_game AS au ON au.game_id = g.id
  JOIN balance AS b ON b.item_id = g.id
WHERE g.private = 0 AND g.closed = 0 AND b.type IN(5,6)
GROUP BY g.id
ORDER BY g.vip DESC, g.benefit DESC

Но так как в разных таблицах разное количество записей, он выводит users и bank с неправильными цифрами, помогите решить эту задачу. Спасибо.

Примерно так:

SELECT g.id, g.title, 
  au.game_id, au.cnt AS users, 
  b.user_id, b.summ AS bank
FROM games AS g
  LEFT JOIN (select game_id, count(*) as cnt 
        from active_user_game 
        group by game_id) AS au ON au.game_id = g. id
  LEFT JOIN (select item_id, sum(summ) as summ 
        from balance 
        where type IN(5,6) 
        group by item_id) AS b ON b.item_id = g.id
WHERE g.private = 0 AND g.closed = 0
ORDER BY g.vip DESC, g.benefit DESC

Кроме того, au.id и b.id

у вас выбирались бы по принципу, известному только mysql (вероятно первое попавшееся значение), поэтому смысла в них нет. А в других субд такой запрос просто не выполнился бы с ошибкой что-то типа «поле au.id, b.id не входят в группу».
Далее, mysql поля в выборке с одинаковыми названиями склеивает в одно поле, поэтому все три id так не получить было все равно, надо было бы пользоваться псевдонимами.

Зарегистрируйтесь или войдите

Регистрация через Google

Регистрация через Facebook

Регистрация через почту

Отправить без регистрации

Почта

Необходима, но никому не показывается

Отправить без регистрации

Почта

Необходима, но никому не показывается

Соединение 3 таблиц в SQL

В SQL можно соединить три таблицы или более, добавив еще одно соединение после первого.

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

Синтаксис

Наиболее распространенный способ соединения трех таблиц выглядит примерно так:

 SELECT *
ИЗ Таблицы1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Таблица 2
    При условии
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Таблица 3
    При условии;

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

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

 ВЫБЕРИТЕ *
ИЗ Таблицы1
ПРИСОЕДИНЯЙТЕСЬ (Таблица 2
            ПРИСОЕДИНЯЙТЕСЬ к таблице 3
            При условии)
При условии;

Пример данных – 3 таблицы

Предположим, у нас есть следующие три таблицы.

Таблица Клиенты :

 +--------------+----------------+-------- -----------------------+--+
| Идентификатор клиента   | ИмяКлиента   | Почтовый идентификатор города   | Номер телефона    |
|----------------------------+----------------+-+ ----------------|
| 1            | Гомер Маккензи | 19586          | (308) 555-0100 |
| 2            | Мардж Пратт    | 33475          | (406) 555-0100 |
| 3            | Влад Бернанке | NULL           | (480) 555-0100 |
| 4            | Барт Питт      | 21692          | (316) 555-0100 |
| 5            | Лиза МакКуин | 12748          | (212) 555-0100 |
| 6            | Стив Симпсон | 17054          | (701) 555-0100 |
| 7            | Винн Аллен     | 12152          | (423) 555-0100 |
| 8            | Виджей Смит | 3673           | (303) 555-0100 |
| 9| Кейси Чин | 23805          | (201) 555-0100 |
| 10           | Борат Ли      | 37403          | (701) 555-0100 |
+--------------+----------------+-+ --+
(затронуто 10 строк)

Таблица Cities :

 +----------+----------------+------ -------------+--------------+
| Идентификатор города   | ГородНазвание       | Идентификатор штатаПровинция   | Население   |
|----------+----------------+-----+- -------------|
| 3673     | Боу Мар        | 6                 | 866          |
| 12152    | Франкьюинг     | 44                | NULL         |
| 12748    | Гаспорт        | 33                | 1248         |
| 21692    | Медицинский домик | 17                | 2009         |
| 26483    | Долина Пиплз | 3                 | 428          |
| 33475    | Сильванит      | 27                | 103          |
| 17054    | Джесси         | 35                | 25           |
| 19586    | Лиско          | 28                | NULL         |
| 37403    | Уимблдон      | 35                | 216          |
+----------+----------------+-----+- -------------+
(затронуто 9 строк)

StateProvinces 9Таблица 0024:

 +---------------------------------+-------+-- -------------------+-------------+---------------+
| Идентификатор штатаПровинция   | Код штатаПровинция   | StateProvinceName   | Идентификатор страны   | Население   |
|-------------------+---------------------+------- --------------+--------------+--------------|
| 3                 | AZ                  | Аризона             | 230         | 6891688      |
| 6                 | CO                  | Колорадо            | 230         | 5698265      |
| 17                | КС                  | Канзас              | 230         | 2893957      |
| 28                | NE                  | Небраска            | 230         | 1943256      |
| 31                | Нью-Джерси                  | Нью-Джерси          | 230         | 8899339      |
| 33                | Нью-Йорк                  | Нью-Йорк            | 230         | 20437172     |
| 35                | НД                  | Северная Дакота        | 230         | 723393       |
| 44                | ТН                  | Теннесси           | 230         | 6495978      |
+-------------------+----------------------+------- --------------+--------------+--------------+
(затронуто 8 рядов)

`Пример 1.`

 Внутреннее соединение 3 таблицы Наиболее популярным типом соединения является внутреннее соединение, поэтому мы начнем с него.
 Вот пример соединения трех вышеприведенных таблиц с двумя внутренними соединениями.
 ВЫБЕРИТЕ
    s.StateProvinceName,
    ci.CityName,
    cu.CustomerName
ИЗ ГосударствоПровинции s
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Города AS ci
ON ci.StateProvinceID = s.StateProvinceID
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Клиенты у.е.
ПО cu.PostalCityId = ci.CityId; 
 Результат:
 +---------------------+--+---- ------------+
| StateProvinceName   | ГородНазвание       | ИмяКлиента   |
|------------------------------------+--+----------- ------|
| Небраска            | Лиско          | Гомер Маккензи |
| Канзас              | Медицинский домик | Барт Питт      |
| Нью-Йорк            | Гаспорт        | Лиза МакКуин |
| Северная Дакота        | Джесси         | Стив Симпсон |
| Теннесси           | Франкьюинг     | Винн Аллен     |
| Колорадо            | Боу Мар        | Виджей Смит |
| Северная Дакота        | Уимблдон      | Борат Ли      |
+-----------------------------------+--+----------- ------+
(затронуто 7 рядов) 
 Пример 2.
 Комбинирование типов соединений Вы можете комбинировать типы соединений при объединении трех или более таблиц.
 Вот пример объединения внутреннего соединения с левым соединением.
 ВЫБЕРИТЕ
    s.StateProvinceName,
    ci.CityName,
    cu.CustomerName
ИЗ ГосударствоПровинции s
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Города AS ci
ON ci.StateProvinceID = s.StateProvinceID
LEFT JOIN Клиенты у.е.
ПО cu.PostalCityId = ci.CityId; 
 Результат:
 --------------------+----------------+----- -----------+
| StateProvinceName   | ГородНазвание       | ИмяКлиента   |
|------------------------------------+--+----------- ------|
| Колорадо            | Боу Мар        | Виджей Смит |
| Теннесси           | Франкьюинг     | Винн Аллен     |
| Нью-Йорк            | Гаспорт        | Лиза МакКуин |
| Канзас              | Медицинский домик | Барт Питт      |
| Аризона             | Долина Пиплз | NULL           |
| Северная Дакота        | Джесси         | Стив Симпсон |
| Небраска            | Лиско          | Гомер Маккензи |
| Северная Дакота        | Уимблдон      | Борат Ли      |
+-----------------------------------+--+----------- ------+
(затронуто 8 рядов) 
 В этом случае у нас есть город (Долина Пиплс), в котором еще нет клиентов.
 Причина, по которой мы теперь можем видеть эту информацию, заключается в том, что левое соединение возвращает строки, содержащие данные в левой таблице, даже если в левой таблице нет соответствующих строк.
 Предыдущий пример, объединяющий два внутренних соединения, не возвращал эту строку, поскольку внутренние соединения отбрасывают несовпадающие строки из обеих таблиц. Он возвращает строки только в том случае, если в обеих таблицах есть хотя бы одна строка, соответствующая условию соединения.
 Новые образцы данных — 3 разные таблицы 
 В оставшихся примерах мы будем использовать следующие таблицы.
 Таблица  PetTypes :
 +-------------+-----------+
| Идентификатор типа питомца   | Тип питомца   |
|-------------+------------|
| 1           | Птица     |
| 2           | Кот       |
| 3           | Собака       |
| 4           | Кролик    |
+-------------+-----------+
(4 строки затронуты) 
 Таблица  Pets :
 +---------+-------------+---------- -+-----------+-------------+
| Идентификатор домашнего животного   | Идентификатор типа питомца   | Идентификатор владельца   | Имя питомца   | Дата рождения        |
|---------+--------------+-----------+------------+- -----------|
| 1       | 2           | 3         | Пушистый    | 20. 11.2020 |
| 2       | 3           | 3         | Получить     | 2019-08-16 |
| 3       | 2           | 2         | Скретч   | 2018-10-01 |
| 4       | 3           | 3         | Виляние       | 2020-03-15 |
| 5       | 1           | 1         | Твитнуть     | 2020-11-28 |
| 6       | 3           | 4         | Пушистый    | 2020-09-17 |
| 7       | 3           | 2         | Кора      | NULL       |
| 8       | 2           | 4         | Мяу      | NULL       |
+---------+-------------+-----------+------------+- -----------+
(затронуты 8 строк) 
 Таблица  Владельцы :
 +-----------+-------------+------------+-------- ----------------------+-----+
| Идентификатор владельца   | Имя   | Фамилия   | Телефон          | Электронная почта             |
|-----------+-------------+-------------+---------- ------+-------------------|
| 1         | Гомер       | Коннери    | (308) 555-0100 | [электронная почта защищена] |
| 2         | Барт        | Питт       | (231) 465-3497 | [электронная почта защищена]  |
| 3         | Нэнси       | Симпсон    | (489) 591-0408 | NULL              |
| 4         | Борис       | Трамп      | (349) 611-8908 | NULL              |
| 5         | Вуди       | Иствуд | (308) 555-0112 | [электронная почта защищена] |
+-----------+-------------+------------+---------- ------+-------------------+ 
 Обратите внимание, что:
 Столбец  PetTypeId  таблицы  Pets  является внешним ключом  PetTypeId  таблицы  PetTypes  (которая является первичным ключом этой таблицы).
 Столбец  OwnerId  таблицы  Pets  является внешним ключом  Столбец OwnerId  таблицы  Owners .
 Пример 3. Левое соединение 3 таблиц 
 Давайте выполним соединение трех таблиц, используя два левых соединения.
 Вот пример выполнения двух левых объединений для этих таблиц.
 ВЫБЕРИТЕ
    п.имя питомца,
    pt.PetType,
    CONCAT(o.FirstName, ' ', o.LastName) AS PetOwner
ОТ владельцев o ВЛЕВО ПРИСОЕДИНЯЙТЕСЬ к домашним животным p
    ON p.OwnerId = o.OwnerId
LEFT JOIN PetTypes pt
    ON p.PetTypeId = pt.PetTypeId; 
 Результат:
 +-----------+------------+----------------+
| Имя питомца   | Тип питомца   | PetOwner       |
|-----------+------------+----------------|
| Твитнуть     | Птица     | Гомер Коннери |
| Скретч   | Кот       | Барт Питт      |
| Кора      | Собака       | Барт Питт      |
| Пушистый    | Кот       | Нэнси Симпсон |
| Получить     | Собака       | Нэнси Симпсон |
| Виляние       | Собака       | Нэнси Симпсон |
| Пушистый    | Собака       | Борис Трамп    |
| Мяу      | Кот       | Борис Трамп    |
| NULL      | NULL      | Вуди Иствуд |
+-----------+------------+----------------+
(9строки затронуты) 
 Здесь у нас есть владелец домашнего животного, у которого нет домашнего животного. Мы можем убедиться в этом, взглянув на столбец  Pets.OwnerId  и увидев, что в таблице  Owners  нет значения, соответствующего  OwnerId  Вуди Иствуда.
 Пример 4. Правое объединение 3 таблиц 
 Правое объединение противоположно левому. Вот пример использования тех же трех таблиц.
 ВЫБЕРИТЕ
    п.имя питомца,
    pt.PetType,
    CONCAT(o.FirstName, ' ', o.LastName) AS PetOwner
ОТ домашних животных p ПРАВО ПРИСОЕДИНЯЙТЕСЬ к владельцам o
    ON p.OwnerId = o.OwnerId
ПРАВОЕ ПРИСОЕДИНЕНИЕ PetTypes pt
    ON p.PetTypeId = pt.PetTypeId; 
 Результат:
 +-----------+------------+---------------+
| Имя питомца   | Тип питомца   | Владелец домашнего животного      |
|-----------+------------+----------------|
| Твитнуть     | Птица     | Гомер Коннери |
| Пушистый    | Кот       | Нэнси Симпсон |
| Скретч   | Кот       | Барт Питт |
| Мяу      | Кот       | Борис Трамп |
| Получить     | Собака       | Нэнси Симпсон |
| Виляние       | Собака       | Нэнси Симпсон |
| Пушистый    | Собака       | Борис Трамп |
| Кора      | Собака       | Барт Питт |
| NULL      | Кролик    | |
+-----------+------------+---------------+
(9строки затронуты) 
 На этот раз мы получили дополнительный тип питомца (  Кролик  ), но не дополнительного владельца. Это связано с тем, что правые соединения возвращают строки, содержащие данные в правой таблице, даже если в левой таблице нет соответствующих строк.
 Кстати, причина, по которой последний  PetOwner  не равен  NULL  (как и последний  PetName ), заключается в том, что это результат конкатенации строк. Я использовал функцию T-SQL  CONCAT()  для объединения имени и фамилии владельца.
 Пример 5. Полное объединение 3 таблиц 
 Полное объединение похоже на левое и правое объединение в одной. Он возвращает все строки, если в одной из таблиц есть совпадающие данные.
 ВЫБЕРИТЕ
    п.имя питомца,
    pt.PetType,
    CONCAT(o.FirstName, ' ', o.LastName) AS PetOwner
ОТ владельцев o ПОЛНОЕ СОЕДИНЕНИЕ Домашние животные p
    ON p.OwnerId = o.OwnerId
ПОЛНОЕ СОЕДИНЕНИЕ PetTypes pt
    ON p.PetTypeId = pt.PetTypeId; 
 Результат:
 +-----------+-----------+----------------+
| Имя питомца   | Тип питомца   | PetOwner       |
|-----------+------------+----------------|
| Твитнуть     | Птица     | Гомер Коннери |
| Скретч   | Кот       | Барт Питт      |
| Кора      | Собака       | Барт Питт      |
| Пушистый    | Кот       | Нэнси Симпсон |
| Получить     | Собака       | Нэнси Симпсон |
| Виляние       | Собака       | Нэнси Симпсон |
| Пушистый    | Собака       | Борис Трамп    |
| Мяу      | Кот       | Борис Трамп    |
| NULL      | NULL      | Вуди Иствуд |
| NULL      | Кролик    | |
+-----------+------------+----------------+
(затронуто 10 рядов) 
 На этот раз мы получаем комбинацию результатов, полученных в двух предыдущих примерах.
 Пример 6. Вложенные соединения 
 Как уже упоминалось, вы также можете выполнять вложенные соединения.
 Вот пример вложенного соединения.
 ВЫБЕРИТЕ
    п.имя питомца,
    pt.PetType,
    CONCAT(o.FirstName, ' ', o.LastName) AS PetOwner
ОТ владельцев o
ЛЕВОЕ СОЕДИНЕНИЕ (Pets p
        LEFT JOIN PetTypes pt
        ON p.PetTypeId = pt.PetTypeId)
ON p.OwnerId = o.OwnerId; 
 Результат:
 +------------+------------+----------------+
| Имя питомца   | Тип питомца   | PetOwner       |
|-----------+------------+----------------|
| Твитнуть     | Птица     | Гомер Коннери |
| Скретч   | Кот       | Барт Питт      |
| Кора      | Собака       | Барт Питт      |
| Пушистый    | Кот       | Нэнси Симпсон |
| Получить     | Собака       | Нэнси Симпсон |
| Виляние       | Собака       | Нэнси Симпсон |
| Пушистый    | Собака       | Борис Трамп    |
| Мяу      | Кот       | Борис Трамп    |
| NULL      | NULL      | Вуди Иствуд |
+-----------+------------+----------------+
(9строки затронуты) 
 база данных - внутреннее соединение с 3 таблицами в mysql 
 Задавать вопрос
 спросил  9 лет, 7 месяцев назад 
 Изменено
 1 год, 3 месяца назад
 Просмотрено
 166 тысяч раз
 Я хочу выбрать данные из других таблиц с помощью внутреннего соединения.
 Это мои таблицы.
 Студент (studentId, имя, фамилия)
Экзамен (examId, имя, дата)
Оценка (gradeId, fk_studentId, fk_examId, оценка)
 
 Я хочу написать выписку, в которой будет указано, на каком экзамене, какой оценке и на какой дате сдавали экзамен учащиеся. Отсортировано по дате.
 Это мое заявление. Он работает, но я хочу убедиться, что я делаю это правильно.
 ВЫБЕРИТЕ
  студент.имя,
  студент.фамилия,
  экзамен.название,
  экзамен.дата,
  класс.класс
С класса
  ВНУТРЕННЕЕ СОЕДИНЕНИЕ студент
    ON student.studentId = класс.gradeId
  ВНУТРЕННЕЕ СОЕДИНЕНИЕ экзамен
    ON экзамен.examId = оценка.gradeId
ЗАКАЗАТЬ ПО дате экзамена
 
 mysql
 база данных
 внутреннее соединение
 7
 Почти правильно.. Посмотрите на объединения, вы ссылаетесь на неправильные поля
 SELECT student.firstname,
       студент.фамилия,
       экзамен. название,
       экзамен.дата,
       класс.класс
  С класса
 ВНУТРЕННЕЕ ПРИСОЕДИНЕНИЕ к учащемуся ON student.studentId =grade.fk_studentId
 INNER JOIN экзамен ON exam.examId =grade.fk_examId
 ЗАКАЗАТЬ ПО дате экзамена
 
 2
 Правильный оператор должен быть:
 SELECT
  студент.имя,
  студент.фамилия,
  экзамен.название,
  экзамен.дата,
  класс.класс
С класса
  ВНУТРЕННЕЕ СОЕДИНЕНИЕ студент
    ON student.studentId = класс.fk_studentId
  ВНУТРЕННЕЕ СОЕДИНЕНИЕ экзамен
    НА экзамен.examId = класс.fk_examId
ЗАКАЗАТЬ ПО дате экзамена
 
 Таблица ссылается на другую на основе определенного отношения внешнего ключа. Вы должны правильно указать идентификаторы, если хотите, чтобы данные отображались как запрошенные. Таким образом, вы должны ссылаться на идентификаторы на правильные внешние ключи в таблице, а не только на идентификатор, который не определяет правильное отношение
 3
  ВЫБЕРИТЕ
 студент.