Hello,
Please help me out, and I will do my best to understand:
Here’s the tables structure:
tbl_activity
id
entity_id
name
tbl_activity_package_address
id
activity_id
name
publication_date
publication_duration_in_days
tbl_activity_package_address
id
activity_package_id
district
Here’s the query I’m running:
SELECT apa.district, t.name
FROM tbl_activity AS t
JOIN tbl_activity_package AS ap ON t.id = ap.activity_id
JOIN tbl_activity_package_address AS apa ON ap.id = apa.activity_package_id
WHERE (DATE_FORMAT(ap.publication_date, '%Y-%m-%d') <= DATE_FORMAT(CURDATE(), '%Y-%m-%d'))
AND (DATE_ADD(DATE_FORMAT(ap.publication_date, '%Y-%m-%d'), INTERVAL ap.publication_duration_in_days DAY) >= DATE_FORMAT(CURDATE(), '%Y-%m-%d'))
GROUP BY apa.district
ORDER BY apa.district
Here’s the result I’m getting:
District Name:
Beja Actividade-2
Braga Actividade-5
Évora Actividade-1
Faro Actividade-9
Leiria Actividade-12
Lisboa Actividade-7
Porto Actividade-11
Here’s the issues:
1) Activity-2 has TWO or MORE districts. How can we show them (comma separated) on the results?
2) Activity-2 has TWO or MORE districts and, one of them is, “Evora”. However, “Evora” already exists on another activity, and ONLY ONE activity with “Evora” should appear.
To say it on another way: If an activity has districts A, B. And another activity has districts B, F.
Only one of those activities should appear, because they BOTH have B.
I’m query once every two years… please have patience.
Thanks in advance.