Abrufen Sie Daten aus mehreren Tabellen mit mehreren bis zu vielen Beziehungen ab ⇐ MySql

[Anonymous]

Ich habe 4 Tabellen. < /p>

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 

= project_job .

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

in einem CSV -Format als Spalte in Project_Job Tabelle, dh:

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

Tabelle.
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

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.>