Как правильно связать 4 таблицы SQL?

Question

[MdaUZH]

Всем привет, подскажите пожалуйста как правильно связать 4 таблицы и сделать выборку..

Есть 4 таблицы:

1. users:
id | name | surname // пользователи
2. lessons
id | group_id| date //рассписание уроков
3. vizit
id | lesson_id| user_id |was// посещения
4. in_groups
id | user_id| group_id // кто в группе

Нужно вывести такую таблицу посещений:


Как это правильно сделать?
Даже представления не имею как это связать, чтобы не спутались пользователи..

Answer 1

[Максим Тимофеев]

Не понимаю зачем таблица vizit, если я правильно понял то она должна быть такой:
id | user_id | lesson_id
Но наверное все должно выглядеть так:
1. users: id | name | surname | group_id
2. lesson id | title // инфо урока
3. lesson_date id | date // даты уроков
4. vizit id | lesson_id | user_id // посещения
5. group id | title // инфо группы

Comments

[MdaUZH]

Максим Тимофеев, отметка был или нет пользователь )
если was = 0 то не был, а если was = 1, то был

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

[MdaUZH]

Максим Тимофеев забыл поле user_id, спасибо)

[Максим Тимофеев]

MdaUZH: Подправил ответ, так наверное правильнее

[MdaUZH]

Максим Тимофеев: нет, ученик может быть сразу в нескольких группах, потому и отдельная таблица для этого)
И информации о уроке нет, только дата и у какой группы и все

[Максим Тимофеев]

MdaUZH: Тогда да, в отдельной таблице, а в остальном все так же

Answer 2

[Rsa97]

Именно в таком виде непосредственно из базы не выбрать, надо будет преобразовывать на клиенте.

SELECT `u`.`id`, `u`.`name`, `u`.`surname`, `l`.`id`, `l`.`date`, 
       `v`.`was`
  FROM `users` AS `u`
  JOIN `in_groups` AS `g` ON `g`.`user_id` = `u`.`id`
  LEFT JOIN `lessons` AS `l` ON `l`.`group_id` = `g`.`group_id`
  LEFT JOIN `visit` AS `v` ON `v`.`lesson_id` = `l`.`id` 
    AND `v`.`user_id` = `u`.`id`
  ORDER BY `u`.`surname`, `u`.`name`, `u`.`id`, `l`.`date`, `l`.`id`

P.S. В таблицах `visit` и `in_groups` поле `id` лишнее, там первичные ключи должны образовываться парами (`lesson_id`, `user_id`) и (`user_id`, `group_id`) соответственно.

Answer 3

[Анатолий Шипицын]

Никак. Так-как требуется транспонирование таблицы, а MySQL это не умеет. Наоборот где к примеру вместо столбцов будут строки можно. Так-как ученику нас один. Далее транспонирование делаем в приложении.