Question about SELECT statement.

[michael123]

I have three tables.

table1:
|-------|-----------|
| Name | Studyarea |
|------|-----------|
| Joe | 1 |
| Joe | 2 |
| Chris | 1 |
| Mike | 3 |
|-------|----------|

table2:
|-----------|-----------|
| Studyarea | CourseID |
|-----------|-----------|
| 1 | 1,2 |
| 2 | 2 |
| 3 | 2,3 |
|-----------|-----------|

table3:
|-----------|-------------|
| CourseID | Course Name |
|-----------|-------------|
| 1 | Hardware |
| 2 | Programming |
| 3 | Networking |
|-----------|-------------|

I am kind of confused how I can write statement in Mysql to list each student name with the course name, like:
Joe--Hardware
Joe--Programming
Chris--Hardware
Mike--Networking

In table2, the courseID is in format of contactnated string.

Need help, thanks in advance.

[chrisbliss18]

Your data isn't correct. In table2, you have multiple IDs in some of the rows:

table2:

|-----------|-----------|
| Studyarea | CourseID  |
|-----------|-----------|
| 1         | 1,2       |
| 2         | 2         |
| 3         | 2,3       |
|-----------|-----------|

This is incorrect. Each row should only have one ID per column. If a Studyarea has more than one CourseID, then there will be more than one row with that Studyarea:

table2:

|-----------|-----------|
| Studyarea | CourseID  |
|-----------|-----------|
| 1         | 1         |
| 1         | 2         |
| 2         | 2         |
| 3         | 2         |
| 3         | 3         |
|-----------|-----------|

Now you can leave the IDs as concatinated strings, but that breaks database design and requires more work. What you would have to do is use a query to merge the table1 and table2 data, split the values out of the CourseID values, and execute additional queries. I can't see anyone designing a database this way unless they had no idea what they were doing. Is there a reason why you want it to be this way?

P.S. Use code tags so that your data looks nice. It's hard to look at your data when it doesn't line up.