**Assignment:**  

I need to select data with many to many relations and search for a solution with a good performance. I have currently two working solutions (see below).

**Example / Description:**  

Competitions are promoted by organisations. A competition can have none, one or more organisations as a promoter. I need each competition only once in the result with an ID list of the promoters.

**Data structure:**  

 - Table "competition": (id, name)  

 - Table "organisation": (id, name)  

 - Table "competition\_organisation": (competitionID, organisationID)  

**Required result:**  

|id|name|promoter\_list|  

|1|UEFA Champions League|1241|  

|2|FIFA World Cup|1240|  

|3|FIFA World Cup Preliminaries - Africa|1240, 1242|  

**Development platform:** Cold Fusion

**Database:** MySQL

**Additional notes based on given answers:**  

- The primary intension of my question is finding a way to handle these kind of relations better than i did it in the past. Competitions are just one example where i need that.  

- I tried to keep it more simple and maybe i omitted one fact to much. In my application, i also need the organisation names. For that reason, i joined the organisaton table.  

- A competition has more related information than i described in this example. The queries in my application uses several joins to other tables.

---

**Solution 1:**  

- Query to select the competition data  

- Loop over result  

- Store each record in an array inside the loop  

- Additional query to select the promoter for each record in the result / loop  

- Add the promoter id's with another query loop to the array  

**Main Query:**

```
SELECT competition.id, competition.name
FROM competition
WHERE ...

```

**Additonal promoter query inside the loop:**

```
SELECT DISTINCT organisation.id 
FROM organisation 
INNER JOIN competition_organisation 
ON competition_organisation.organisationID = organisation.id 
WHERE competition_organisation.competitionID = competition.id[currentrow]#

```

---

**Solution 2:**  

- Use only one query with a sub select  

- Loop over result  

- Store each record in an array inside the loop  

```
SELECT competition.id, competition.name,
(
    SELECT CONVERT(GROUP_CONCAT(organisation.id SEPARATOR ', ') USING utf8)
    FROM organisation
    WHERE organisation.id in 
    (
        SELECT DISTINCT competition_organisation.organisationID 
        FROM competition_organisation
        WHERE competition_organisation.competitionID = competition.id
    )
) AS promoter_list
FROM competition
WHERE ...

```

---

**Solution 3 (proposed by Spencer7593):**  

```
SELECT c.id, 
       c.name,
       CONVERT(GROUP_CONCAT(DISTINCT o.id ORDER BY o.id) USING utf8) AS promoter_id_list,
       CONVERT(GROUP_CONCAT(DISTINCT o.name ORDER BY o.id) USING utf8) AS promoter_list
FROM competition c
LEFT JOIN competition_organisation c_o ON c_o.competitionID = c.id
LEFT JOIN organisation o ON o.id = c_o.organisationID
GROUP BY c.id, c.name

```

(I changed the code a little bit and added organisation names)  

---

**Solution 4 (proposed by Thorsten Kettner, optimized by Rick James):**  

```
SELECT id, name,
       ( SELECT CONVERT(GROUP_CONCAT(organisationID SEPARATOR ', ') USING utf8)         
             FROM competition_organisation
             WHERE competitionID = c.id
       ) AS promoter_id_list,
       ( SELECT CONVERT(GROUP_CONCAT(organisation.name SEPARATOR ', ') USING utf8)          
             FROM competition
             left join competition_organisation on competition_organisation.competitionID = competition.id
             left join organisationen on organisationen.id = competition_organisation.organisationID
             WHERE competitionID = c.id
       ) AS promoter_list
    FROM competition AS c

```

(Also added organisation names, hopefully in the right way)

---

**Performance comparison:**  

Solution 1 - 100 records: ~30ms + (100 x ~1ms) = ~130ms  

Solution 1 - 1000 records: ~70ms + (1000 x ~1ms) = ~1070ms

Solution 2 - 100 records: ~5500ms  

Solution 2 - 1000 records: ~48000ms  

Solution 3 - 100 records: ~120ms  

Solution 3 - 1000 records: ~210ms  

Solution 4 - 100 records: ~110ms  

Solution 4 - 1000 records: ~200ms  

---

As you can see, solution 2 has a miserable performance. 

* Are there options to optimize the solution 2 query to increase the performance significantly?
* Are there alternative solutions i dont have in mind?
* Or should i stay at solution 1?

---

**Conclusion:**  

I've decided to take Solution 3 by Spencer. The Performance of 3 and 4 are nearly identical. But the code of 3 is easier and perfectly matches with my existing queries, especialy with thier left joins.

I am very satisfied with the result. Perfomance has been increased greatly and i need less code / files in the future.

Thank you very much for your assistance!