Как сформулировать запрос, чтобы показать все курсы, пройденные человеком

У меня возникли проблемы с правильной формулировкой запроса MySQL. Все, что я пробовал, не дает мне того, что нужно, или дает синтаксическую ошибку.

У меня есть три таблицы: «Клиенты», «Курсы» и «Курсы для клиентов».

В таблице «Клиенты» указаны только основные координаты человека: идентификатор, имя, адрес, адрес электронной почты и т. д.

+----------+-----------------------------+------+
| ClientID | Name        | Address       | etc. |
+----------+-----------------------------+------+
|    10    | Joe Smith   | 1 Main St.    | ...  |
|    20    | Bob Smith   | 2 Main St.    | ...  |
|   ...    | ... ...     | ... ... ...   | ...  |
+----------+-----------------------------+------+

В таблице «Курсы» хранится название курса и его идентификатор.

+----------+-----------------------+
| CourseID | Name                  |
+----------+-----------------------+
|    100   | Intro. to Subject     |
|    200   | Intermediate Subject  |
|    300   | Advanced Subject      |
|    ...   | ... ... ... ...       |
+----------+-----------------------+

В таблице CoursesForClients есть CourseID и ClientID. Данный клиент может пройти несколько курсов, поэтому для каждого курса, который прошел клиент, есть строка с идентификатором человека и идентификатором курса.

+----------+----------+
| CourseID | ClientID |
+----------+----------+
|   100    |     1    |
|   200    |     1    |
|   300    |     1    |
|   100    |     2    |
|   200    |     2    |
|   ...    |    ...   |
+----------+----------+

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

10:Joe Smith
1 Main St.
Somewhere, AL

Intro. to Subject
Intermediate Subject
Advanced Subject
---------------------------

20:Bob Smith
2 Main St.
Somewhere, AL

Intro. to Subject
Intermediate Subject

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

Есть еще одна особенность, заключающаяся в том, что есть еще одна таблица, в которой перечислены оценки за курс для клиента, и этот GradeID также хранится в таблице CoursesForClients, и есть еще одна таблица оценок с идентификатором и описанием оценки. Но я не буду беспокоиться об этом прямо сейчас. На данный момент все, что мне нужно, это просто показанный основной вывод, как описано выше.

Похоже, что для этого должно быть легко настроить запрос с помощью JOIN и, возможно, GROUP BY, но у меня здесь блок, и я не могу понять это правильно. Таким образом, любая помощь будет очень признательна. Благодарю вас!


mysql group-by left-join right-join
person Lew    schedule 07.06.2015    source источник
comment
И вы хотите сделать все это без кода уровня представления?   -  person Strawberry    schedule 08.06.2015
comment
Ну, я опускаю посторонние вещи. я могу заниматься презентацией; это запрос, с которым мне нужна помощь.   -  person Lew    schedule 08.06.2015
comment
Независимо от того, какой тип соединения вы используете в SQL, на выходе будет табличное представление (то есть строки/столбцы), так как же вы ожидаете, что клиент с несколькими курсами появится только один раз? Его имя (или идентификатор) будет повторяться в каждой строке курса, если только вы не создадите своего рода объект DataSet (.NET), содержащий две таблицы с установленными отношениями Master-Details. С наилучшими пожеланиями,   -  person Alexander Bell    schedule 08.06.2015
comment
Итак, вы говорите, что невозможно настроить запрос, который будет возвращать все курсы для человека, причем человек будет отличаться? Например, скажите: Joe Course1, Course2, Course3 Bob Course2 Tom Course1, Course2 Это правильно? Я правильно интерпретирую ваш комментарий? Я бы подумал, что это может сделать какая-то комбинация DISTINCT или GROUP BY, например SELECT DISTINCT Name FROM Clients.   -  person Lew    schedule 08.06.2015

Ответы (1)


arrow_upward
2
arrow_downward

SQL работает с таблицами. По определению таблица состоит из набора строк, каждая из которых имеет одинаковые столбцы. Ваш запрос даст набор результатов, который дублирует информацию клиента для каждого курса, который она прошла.

Ваш уровень представления будет форматировать эту таблицу, замечая первую строку каждого нового клиента и разбивая заголовок клиента. Вы сделаете это в php, Java, Crystal Reports или какой-либо подобной технологии для презентаций.

Ваш запрос примерно такой.

    SELECT a.id, a.name, a.address, a.etc,
           c.Name
      FROM Clients a
      JOIN CoursesForClients b USING(ClientID)
      JOIN Courses c USING(CourseID)
      ORDER BY a.id, c.CourseID

@Strawberry делает хорошее замечание о ловушке использования USING(). Вот тот же запрос на ON.

    SELECT a.id, a.name, a.address, a.etc,
           c.Name
      FROM Clients a
      JOIN CoursesForClients b ON a.ClientID = b.ClientID
      JOIN Courses c ON b.CourseID = c.CourseID
      ORDER BY a.id, c.CourseID
person Community    schedule 08.06.2015
comment
Лично мне не нравится синтаксис USING. Я думаю, что это особенно сбивает с толку новичков, особенно когда запросы становятся более сложными. Жалко, что этот викифицирован, но, может быть, это только я. - person Strawberry; 08.06.2015
comment
Хорошо. Если мне нужно добавить немного PHP для настройки заголовка, в котором сведения о клиенте появляются только один раз, это нормально — нет проблем. Похоже, это единственный способ сделать это, и что бы я ни думал об использовании DISTINCT, скажем, в имени клиента, это ошибочное мышление. - person Lew; 08.06.2015
comment
@Лью Да. Вопросы отображения данных не следует путать с вопросами хранения и поиска данных. Существуют хаки, которые означают, что вы можете создать вывод, подобный тому, который вы себе представляете, но это не значит, что вы должны. - person Strawberry; 08.06.2015
comment
@Strawberry, вы правы насчет ИСПОЛЬЗОВАНИЯ ... Я, хм, торопился и сидел на планшете, когда писал этот ответ, а в ИСПОЛЬЗОВАНИИ немного меньше знаков препинания. Я не понимаю вашу точку зрения о позоре пометки ответа как вики сообщества. Я сделал это именно для того, чтобы вы могли отредактировать его, если хотите; вы предоставили ценный ответ в качестве комментария. - person O. Jones; 08.06.2015
comment
Возможно, я неправильно понял этот аспект SO. Во всяком случае, никакого вреда. - person Strawberry; 08.06.2015