0

I'm having trouble formulating a MySQL query correctly. Everything I've tried doesn't give me what's needed, or gives a syntax error.

I have three tables: Clients, Courses, and CoursesForClients.

The Clients table just has basic coordinates for a person: ID, Name, Address, email, etc.

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

The Courses table stores the course name and its ID.

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

The CoursesForClients table has the CourseID and ClientID. A given Client can have taken multiple courses, so for every course that a Client has taken, there's a row, with the person's ID and the Course ID.

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

Now, what I need is to be able to list the Client - just once - together with all the Courses she has taken. So, the result of the query might look like this:

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

So this output reflects the relationships between the Client and the Course. The key thing here is that, no matter how many Courses a Client has taken, the Client's particulars appear only once, followed by the list of all the courses she's taken.

There's an additional twist in that there's another table that lists the Grade for the Course for the Client, and that GradeID is also stored in the CoursesForClients table, and there's another table of Grades, with ID and Grade Description. But I won't worry about this right now. For now, all I want is just the basic output shown, as described above.

It looks like it should be easy to set up a query for this, with a JOIN and maybe a GROUP BY, but I'm having a block here and can't seem to get it right. So, any help will be hugely appreciated. Thank you!

Lew
  • 1,431
  • 1
  • 16
  • 22
  • And you want to do all this with no presentation layer code? – Strawberry Jun 07 '15 at 23:46
  • Well, I'm omitting extraneous stuff. I can deal with presentation; it's the query I need help with. – Lew Jun 07 '15 at 23:50
  • 1
    Regardless of what type of join you are using in SQL, the output will be a tabular representation (i.e. rows/columns), so how do you expect the client with multiple courses to appear only once? His name (id, whatsoever) will be repeated in every course row unless you create a sort of DataSet (.NET) object containing two Tables with established Master-Details relationship. Best regards, – Alexander Bell Jun 08 '15 at 00:19
  • So what you're saying is that it's not possible to set up a query that will return all courses for a person, with the person being distinct? Like, say: `Joe Course1, Course2, Course3` `Bob Course2` `Tom Course1, Course2` Is that correct? Am I interpreting your comment correctly? I would have thought that some combination of DISTINCT or GROUP BY could do this, such as `SELECT DISTINCT Name FROM Clients.` – Lew Jun 08 '15 at 00:28

1 Answers1

2

SQL deals in tables. By definition a table has a bunch of rows, each of which has the same columns as each other. Your query is going to yield a result set that duplicates the client's information for each course she took.

Your presentation layer is going to format that table, by noticing the first row of each new client and breaking out the client header. You'll do that in php or Java or Crystal Reports or some such presentation tech.

Your query is something like this.

    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 makes a good point about the pitfall of using USING(). Here is the same query on 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
O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • Personally, I dislike USING syntax. I think it's particularly confUSING for newbies, espcially as queries get more complex. A shame then that this one got wikified, but maybe that's just me. – Strawberry Jun 08 '15 at 00:13
  • OK, fine. If I need to add some PHP to set up a header that has the Client details appearing only once, that's fine - no problem. It's looking like that's the only way to do it, and whatever I had thought about using DISTINCT on the Client name, say, is faulty thinking. – Lew Jun 08 '15 at 00:42
  • @Lew Yes. Issues of data display should not be confused with issues of data storage an retrieval. There are hacks that mean that you *can* construct output like the kind you're imagining, but that's not to say you *should*. – Strawberry Jun 08 '15 at 10:02
  • @Strawberry you're right about USING ... I was, um, in a hurry and on a tablet when I wrote that answer, and USING has a little less punctuation. I don't understand your point about the "shame" of marking the answer as community wiki. I did that precisely so you could edit it if you wanted to; you contributed a valuable answer as a comment. – O. Jones Jun 08 '15 at 10:55
  • Perhaps I've misunderstood that aspect of SO. Anyway, no harm done. – Strawberry Jun 08 '15 at 13:40