CODEWITHSUNDEEP
sqlmariadb
Kathara
Kathara

Reputation: 1290

SQL MariaDB getting data from 7 tables including mm-tables resulting in too many unwanted rows

I'm struggling with getting data from 7 different sql-tables without receiving too many rows.

I have the following (simple) query which retrieves data from 7 different tables:

SELECT h.name, h.address, h.zipcode, h.city, h.association, r.name_de, f.first_name, f.last_name, f.email, p.year, j.name
FROM `tx_gipdhotels_domain_model_hotel` AS h

JOIN `tx_gipdhotels_hotel_jobs_mm` AS hj ON h.uid = hj.uid_local
JOIN `tx_gipdhotels_domain_model_jobs` AS j ON j.uid = hj.uid_foreign
JOIN `tx_gipdhotels_hotel_participations_mm` AS hp ON h.uid = hp.uid_local
JOIN `tx_gipdhotels_domain_model_participations` AS p ON p.uid = hp.uid_foreign
JOIN `tx_gipdhotels_domain_model_region` AS r ON r.uid = h.region
JOIN `fe_users` AS f ON f.uid = h.feuser

As you can see there are two many-to-many-relationships between the tables. These two tables aren't related (except through the h table). Now the problem is that this results in receiving a row for each possible combination of these mm-tables.

Example:

table 1 hotel

|-----------|------------|----------|----------|
|    uid    |    name    |   jobs   |   part   |
|...........|............|..........|..........|
|     1     |     ab     |     3    |     2    |
|           |            |          |          |

table 2 jobs

|-----------|------------|
|    uid    |    name    |
|...........|............|
|     1     |    tech    |
|     2     |     cs     |
|     3     |    perf    |
|           |            |

table 3 part

|-----------|------------|
|    uid    |    name    |
|...........|............|
|     1     |    abcd    |
|     2     |    efgh    |
|           |            |

With this combination (including mm-tables for hotel_jobs and hotel_part) I would receive 6 rows for one hotel only and in each row only one value would differ from another row:

result:

|-----------|------------|----------|----------|
|    uid    |    name    |   job    |   part   |
|...........|............|..........|..........|
|     1     |     ab     |   tech   |   abcd   |
|     1     |     ab     |   tech   |   defg   |
|     1     |     ab     |    cs    |   abcd   |
|     1     |     ab     |    cs    |   defg   |
|     1     |     ab     |   perf   |   abcd   |
|     1     |     ab     |   perf   |   defg   |
|           |            |          |          |

It would be lovely if I could retrieve this data in one single row like the following:

wanted result:

|-----------|------------|--------------------|----------------|
|    uid    |    name    |         job        |      part      |
|...........|............|....................|................|
|     1     |     ab     |   tech, cs, perf   |   abcd, efgh   |
|           |            |                    |                |

I can't figure out how to get the wanted result, it exceeds my experience and knowledge so I'm asking you, do you know how to achieve this with a single query?

I've googled quite a bit and I have found the STUFF() method but it's not supported in MariaDB. In some question here on stack someone has done something similar with a cast but I didn't understand it too well and I didn't know how to adapt this to my problem...

I'm using MariaDB and the query will be made from php. There is no way of changing the data structure of the tables.

Any help and explanations would be greatly appreciated.

Upvotes: 0

Views: 49

Answers (2)

Abdul Qayyum
Abdul Qayyum

Reputation: 1052

I hope this will work, try it, if there is any error, we are gonna fix it. 

SELECT 
    h.name, 
    h.address,
    h.zipcode,
    h.city,
    h.association,
    GROUP_CONCAT(DISTINCT p.year SEPARATOR ', '),
    GROUP_CONCAT(DISTINCT j.name SEPARATOR ', '),
    r.name_de,
    f.first_name,
    f.last_name,
    f.email,
    h.tstamp,
    h.crdate 
    FROM tx_gipleasedisturbhotels_domain_model_hotel AS h
    JOIN `tx_gipleasedisturbhotels_hotel_jobs_mm` AS hj 
    ON h.uid = hj.uid_local
    JOIN `tx_gipleasedisturbhotels_domain_model_jobs` AS j 
    ON j.uid = hj.uid_foreign
    JOIN `tx_gipleasedisturbhotels_hotel_participations_mm` AS hp 
    ON h.uid = hp.uid_local
    JOIN `tx_gipleasedisturbhotels_domain_model_participations` AS p 
    ON p.uid = hp.uid_foreign
    JOIN `tx_gipleasedisturbhotels_domain_model_region` AS r 
    ON r.uid = h.region
    JOIN `fe_users` AS f 
    ON f.uid = h.feuser
    GROUP BY h.name
    ORDER BY h.name ASC

Upvotes: 2

Kathara
Kathara

Reputation: 1290

Thanks to @jarlh I found the solution:

SELECT h.name, h.address, h.zipcode, h.city, h.association,
GROUP_CONCAT(DISTINCT p.year SEPARATOR ', '),
GROUP_CONCAT(DISTINCT j.name SEPARATOR ', '),
r.name_de, f.first_name, f.last_name, f.email, h.tstamp, h.crdate

FROM `tx_gipleasedisturbhotels_domain_model_hotel` AS h

JOIN `tx_gipleasedisturbhotels_hotel_jobs_mm` AS hj ON h.uid = hj.uid_local
JOIN `tx_gipleasedisturbhotels_domain_model_jobs` AS j ON j.uid = hj.uid_foreign
JOIN `tx_gipleasedisturbhotels_hotel_participations_mm` AS hp ON h.uid = hp.uid_local
JOIN `tx_gipleasedisturbhotels_domain_model_participations` AS p ON p.uid = hp.uid_foreign
JOIN `tx_gipleasedisturbhotels_domain_model_region` AS r ON r.uid = h.region
JOIN `fe_users` AS f ON f.uid = h.feuser

GROUP BY h.name
ORDER BY h.name ASC

It's a combination of GROUP_CONCAT and GROUP BY. It has to be grouped by the field which you want to have only once. To get all mm-values to one single cell you'll have to use GROUP_CONCAT on those fields in the SELECT statement.

With this query I receive the wanted result. Maybe this will be helpful to someone else as well. ;)

Upvotes: 0

Related Questions