I have 4 tables.
project
--------------------------------------
| project_id | name | client_id |
--------------------------------------
| 1 | Make Safe | 12 |
| 2 | Quote | 55 |
| 3 | Assist | 2 |
--------------------------------------
project_job
---------------------------------------------------------
| project_job_id | project_id | project_number| manager |
---------------------------------------------------------
| 1 | 1 | 1 | Steve |
| 2 | 1 | 2 | Harry |
| 3 | 2 | 1 | Paul |
| 4 | 3 | 1 | Sally |
---------------------------------------------------------
project_job_trade
---------------------------------------
| project_job_id| project_job_trade_id|
---------------------------------------
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 4 |
| 3 | 3 |
| 4 | 1 |
| 4 | 2 |
---------------------------------------
PRIMARY KEY (project_job_id, project_job_trade_id)
master_project_job_trade
--------------------------------------
| project_job_trade_id | name |
--------------------------------------
| 1 | Electrician |
| 2 | Plumber |
| 3 | Capenter |
| 4 | Arborist |
--------------------------------------
from top to bottom:
project.project_id = project_job.project_id = 1:many
project_job.project_job_id=project_job_trade.project_job_id = 1:many
project_job_trade.project_job_trade_id=master_project_job_trade.project_job_trade_id
Previously I had the project_job_trade.project_job_trade_id in a csv format as a column in project_job table, ie:
project_job
--------------------------------------------------------------------------------
| project_job_id | project_id | project_number| manager | project_job_trade_id |
--------------------------------------------------------------------------------
| 1 | 1 | 1 | Steve | 1,2,3 |
| 2 | 1 | 2 | Harry | 4 |
| 3 | 2 | 1 | Paul | 3 |
| 4 | 3 | 1 | Sally | 1,4 |
--------------------------------------------------------------------------------
I was able to retrieve the trade name associated with each project>project_number, but only the 1st one in the list with a LEFT JOIN, but any others in the list were not displayed, so I was researching a solution. After a lot of reading, it was clear that storing data in a delimited list is not recommended, so i split the data into project_job_trade table.
The issue I am having is that I am unable to figure out how to retrieve the data in this format.
-------------------------------------------------------------------------------------------------
| project_id | name | client_id | project_number| manager | name |
-------------------------------------------------------------------------------------------------
| 1 | Make Safe | 12 | 1 | Steve | Electrician, Plumber, Capenter |
| 1 | Make Safe | 12 | 2 | Harry | Arborist |
| 2 | Quote | 55 | 1 | Paul | Capenter |
| 3 | Assist | 2 | 1 | Sally | Electrician, Arborist |
-------------------------------------------------------------------------------------------------
I did stumble upon GROUP_CONCAT as a way to display multiple trade names in 1 column, which in theory would fix the issue I was having previously with only the first item being returned, but with this additional table I am unable to wrap my head as to how to extract the data and put it all together.
Please help.
Thanks in advance.
idvalues and avoid their equiality (for exdample, project_id - 1 to 3, project_job_id - 11 to 14, and so on).