mysql multiple-subquery group_concat query

Question

I'm trying to show the boroughs and postcodes a particular town in is.

My database is fairly well structured, with a table such as town, postcode and borough. There are also tables for each of the relationships town_postcode & town_borough.

Ideally I want the data returned as:

"Abbey Wood", "SE2", "Bexley, Greenwich" "Barbican", "EC1, EC2", "City of London"

I've tried a few different approaches and I'm close but not there yet.

Any help would be appreciated... :) So far I've tried

SELECT DISTINCT t.town, 
GROUP_CONCAT( DISTINCT p.postcode SEPARATOR ', ' ) AS 'postcode', 
GROUP_CONCAT( DISTINCT b.borough SEPARATOR ', ' ) AS 'borough'
FROM coverage_towns AS t, 
coverage_boroughs AS b, 
coverage_postcodes AS p, 
coverage_towns_boroughs AS tb, 
coverage_towns_postcodes AS tp
WHERE t.id = tp.town_id
AND p.id = tp.postcode_id
AND b.id = tb.borough_id
GROUP BY t.town
ORDER BY t.town ASC

Which returns

"Abbey Wood", "SE2", "Southwark, Hammersmith and Fulham, Tower Hamlets, Wandsworth, Enfield, Newham, LOTS MORE HERE"
"Barbican", "EC1, EC2", "Brent, Greenwich, Kensington and Chelsea, Westminster, Camden, LOTS MORE HERE"

I've also tried

SELECT DISTINCT t.town, (

SELECT SQL_CACHE DISTINCT GROUP_CONCAT( p1.postcode
SEPARATOR ', ' )
FROM coverage_postcodes AS p1
WHERE p1.id = tp.postcode_id
) AS 'postcode', (

SELECT SQL_CACHE DISTINCT GROUP_CONCAT( b1.borough
SEPARATOR ', ' )
FROM coverage_boroughs AS b1
WHERE b1.id = tb.borough_id
) AS 'borough'
FROM coverage_towns AS t, coverage_boroughs AS b, coverage_postcodes AS p, coverage_towns_boroughs AS tb, coverage_towns_postcodes AS tp
WHERE t.id = tp.town_id
AND p.id = tp.postcode_id
AND b.id = tb.borough_id
GROUP BY t.town
ORDER BY t.town ASC

Which returns

"Abbey Wood", "SE2", "Greenwich"
"Acton", "W3", "Greenwich"
"Aldersbrook", "E12", "Greenwich"

Answer 1

First query looks good, just add distinct inside the group_concat, like:

SELECT  t.town
,      GROUP_CONCAT(DISTINCT p.postcode SEPARATOR ', ' ) AS 'postcode'
,      GROUP_CONCAT(DISTINCT b.borough SEPARATOR ', ' ) AS 'borough'
<more code here>
GROUP BY
        t.town

Answer 2

SOLUTION

I came back to the question after a good coffee and the answer presented itself.

SELECT DISTINCT t.town, 
GROUP_CONCAT( DISTINCT p.postcode SEPARATOR ', ' ) AS 'postcode', 
GROUP_CONCAT( DISTINCT b.borough SEPARATOR ', ' ) AS 'borough'
FROM towns AS t, boroughs AS b, postcodes AS p, towns_boroughs AS tb, towns_postcodes AS tp
WHERE (t.id = tp.town_id AND t.id = tb.town_id)
AND (p.id = tp.postcode_id AND b.id = tb.borough_id)
GROUP BY t.town
ORDER BY t.town ASC