Member Avatar for 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.

  • 2 Contributors
  • 1 Reply
  • 113 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by chrisbliss18
Member Avatar for 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.

Edited by TrustyTony because: fixed formatting
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.