There is a SQL query:

To the first table:

SELECT DISTINCT LEFT(Prefix,3) AS `Prefix`, group_concat(distinct `Primary City` separator ', ') AS `Citys` FROM phonelist WHERE State = 'washington' GROUP BY LEFT(Prefix,3)

Gives out:

 Prefix | Citys -------------- 206 | Seattle WA, Richmond Beach WA 360 | Longview WA, Lynden WA, Port Angeles WA 425 | Seattle WA, Renton WA, Everett WA, Bellevue WA 509 | Walla Walla WA, Ellensburg WA, Spokane WA

To the second table:

 SELECT LEFT(Prefix,3), group_concat(distinct `Zip` separator ', ') AS `Zip` FROM Zip where Prefix LIKE '509%'

Gives out:

 Prefix | Zip -------------- 509 | 98926, 99336, 99338, 99337, 98930, 99362

How to combine them so that Prefix from the first column of the first query is used as the Prefix LIKE '509%' instead of 509 , and the resulting Zip column is added to the summary table

Final conclusion:

 Prefix | Citys | Zip -------------------------------------------------------- 206 | Seattle WA, Richmond Beach WA | 9898, 8787, 8787, 8787, 360 | Longview WA, Lynden WA, Port Angeles WA | 8787, 8787, 8787, 8787, 425 | Seattle WA, Renton WA, Everett WA, Bellevue WA | 44654, 67868, 6876 509 | Walla Walla WA, Ellensburg WA, Spokane WA | 98926, 99336, 99338, 99337, 98930, 99362

Is this even possible?)

    1 answer 1

    Perhaps something like this:

     SELECT LEFT(Prefix,3) AS `Prefix`, group_concat(distinct `Primary City` separator ', ') AS `Citys`, (SELECT group_concat(distinct `Zip` separator ', ') FROM Zip Z where Z.Prefix LIKE concat(LEFT(P.Prefix,3),'%') ) `Zip` FROM phonelist P WHERE State = 'washington' GROUP BY LEFT(Prefix,3)

    And something tells me that distinct in this query is not needed.