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 ([code][/code]) so that your data looks nice. It's hard to look at your data when it doesn't line up.