Abrufen Sie Daten aus mehreren Tabellen mit mehreren bis zu vielen Beziehungen ab
Posted: 28 Feb 2025, 06:06
Ich habe 4 Tabellen. < /p>
. = project_job .. =.. =.
Zuvor hatte ich das Projekt project_job_trade . in einem CSV -Format als Spalte in Project_Job Tabelle, dh:
Tabelle.
Das Problem, das ich habe, ist, dass ich nicht herausfinden kann als Möglichkeit, mehrere Handelsnamen in 1 Spalte anzuzeigen, die das Problem theoretisch beheben würde, hatte ich zuvor nur das erste Element, das zurückgegeben wurde. Mit dieser zusätzlichen Tabelle kann ich meinen Kopf jedoch nicht einwickeln, wie die Daten extrahiert werden und alles zusammenfügen.
Bitte helfen Sie.>
Code: Select all
project
Code: Select all
--------------------------------------
| project_id | name | client_id |
--------------------------------------
| 1 | Make Safe | 12 |
| 2 | Quote | 55 |
| 3 | Assist | 2 |
--------------------------------------
< /code>
project_job
Code: Select all
---------------------------------------------------------
| project_job_id | project_id | project_number| manager |
---------------------------------------------------------
| 1 | 1 | 1 | Steve |
| 2 | 1 | 2 | Harry |
| 3 | 2 | 1 | Paul |
| 4 | 3 | 1 | Sally |
---------------------------------------------------------
< /code>
project_job_trade
Code: Select all
---------------------------------------
| project_job_id| project_job_trade_id|
---------------------------------------
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 4 |
| 3 | 3 |
| 4 | 1 |
| 4 | 2 |
---------------------------------------
< /code>
master_project_job_trade
Code: Select all
--------------------------------------
| project_job_trade_id | name |
--------------------------------------
| 1 | Electrician |
| 2 | Plumber |
| 3 | Capenter |
| 4 | Arborist |
--------------------------------------
< /code>
from top to bottom:
project
Code: Select all
project_id
Code: Select all
project_id< /code> = 1: viele
project_job
Code: Select all
project_job_id
Code: Select all
project_job_trade
Code: Select all
project_job_id< /code> = 1: viele
project_job_trade
Code: Select all
project_job_trade_id
Code: Select all
master_project_job_trade
Code: Select all
project_job_trade_id
Zuvor hatte ich das Projekt project_job_trade .
Code: Select all
project_job_trade_id
Code: Select all
project_job
Code: Select all
--------------------------------------------------------------------------------
| 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 |
--------------------------------------------------------------------------------
< /code>
I was able to retrieve the trade name associated with the 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
Das Problem, das ich habe, ist, dass ich nicht herausfinden kann
Code: Select all
-------------------------------------------------------------------------------------------------
| 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 |
-------------------------------------------------------------------------------------------------
< /code>
I did stumble upon GROUP_CONCAT
Bitte helfen Sie.>