I have a big issue for my "traveling offer" project, working 99% OK, but not 100%.
I have main table with offers, where each offer can have set multiple department cities as well as multiple destination cities (this is reduced sample with reduced columns).
For example, I'm offering some travels from England, where department cities can be from London, Leeds and Manchester. Destination cities are Prague, Bratislava, Budapest and Belgrade.
Offer 1 is set to department cities London or Leeds, and destinations are Prague and Budapest. Offer 2 is set to department city London, and destinations are Bratislava and Belgrade. Offer 3 is set to department city Manchester or Leeds, and destination is Prague.
Table offers
----------------------------
id title price
----------------------------
1 Offer 1 title 300 Eur
2 Offer 2 title 250 Eur
3 Offer 3 title 350 Eur
Now relation tables and city name tables
Table departments
----------------------------
id name
----------------------------
1 London
2 Leeds
3 Manchester
relation Table rel_departments
------------------------
offer_id rel_id
------------------------
1 1
1 2
2 1
3 2
3 3
Table destinations
----------------------------
id name
----------------------------
1 Prague
2 Bratislava
3 Budapest
4 Belgrade
relation Table rel_destinations
------------------------
offer_id rel_id
------------------------
1 1
1 3
2 2
2 4
3 1
As SQL result I expect for each offer concatenated values bot for department cities and destination cities
If I search all with following sql I got OK result:
SELECT offers.*,
GROUP_CONCAT(DISTINCT DEPC.name SEPARATOR ', ') AS depCities,
GROUP_CONCAT(DISTINCT DESTC.name SEPARATOR ', ') AS destCities
FROM offers
INNER JOIN `rel_departments` ON (`rel_departments`.`offer_id` = `offers`.`id`)
INNER JOIN `departments` as DEPC ON (DEPC.`id` = `rel_departments`.`rel_id`)
INNER JOIN `rel_destinations` ON (`rel_destinations`.`offer_id` = `offers`.`id`)
INNER JOIN `destinations` as DESTC ON (DESTC.`id` = `rel_destinations`.`rel_id`)
GROUP BY offers.id
result would be okay:
---------------------------------------------------------------------
id title price depCities destCities
---------------------------------------------------------------------
1 Offer 1 title 300 Eur London, Leeds Prague, Budapest
2 Offer 2 title 250 Eur London Bratislava, Belgrade
3 Offer 3 title 350 Eur Leeds, Manchester Prague
And I need results like this whatever WHERE clause is. But, whenever I put where clause, I loose one of results in concatenation. For example, I search for all offers with Prague as a destination. If I add to the end of the sql statement:
where rel_destinations.rel_id=1
result is as following:
---------------------------------------------------------------------
id title price depCities destCities
---------------------------------------------------------------------
1 Offer 1 title 300 Eur London, Leeds Prague
3 Offer 3 title 350 Eur Leeds, Manchester Prague
If you can notice, there is no Budapest in offer 1. What to do to get complete concatenation string... Not that WHERE clause can be more complex, i.e. to search for department city or any other parameter.
Any help is appreciated :)
You need to use a different join with rel_destinations to get the offers with Prague as a destination. Join this with your original query.
SELECT offers.*,
GROUP_CONCAT(DISTINCT DEPC.name SEPARATOR ', ') AS depCities,
GROUP_CONCAT(DISTINCT DESTC.name SEPARATOR ', ') AS destCities
FROM offers
INNER JOIN `rel_departments` ON (`rel_departments`.`offer_id` = `offers`.`id`)
INNER JOIN `departments` as DEPC ON (DEPC.`id` = `rel_departments`.`rel_id`)
INNER JOIN `rel_destinations` ON (`rel_destinations`.`offer_id` = `offers`.`id`)
INNER JOIN `destinations` as DESTC ON (DESTC.`id` = `rel_destinations`.`rel_id`)
INNER JOIN rel_destinations AS d1 ON d1.offer_id = offers.id
WHERE d1.rel_id = 1
GROUP BY offers.id