0

I have a datebase with geo-based content. A field 'geo' determines what countries a certain row is for (could be multiple). I want to have every country code from MySQL for which there should be a different result. If there's no content in the DB specifically for "CN" for instance we can use the 'general' result for rows that are not specifically for a certain country. 

id | geo    |
---+--------+
1  | NL     |
2  | US     |
3  |        |
4  | CA NL  |
5  | US     |

What I want to get back is:

| geo |
+-----+
|     |
| NL  |
| US  | 
| CA  |

I tried playing with CONCAT (SELECT GROUP_CONCAT from db) to get all possible entries found in the database, but that would involve splitting two results (for ID#4) in two rows in order to get something like "NL,US,CA" back, that would already help.

A solution would be to generate a MANY-TO-MANY database for this, which would make it very easy to select the countries used in the table. Another way could be to get everything GROUPED by GEO and use PHP to determine what countries to use, but I can't stop thinking there must be a cleaner way to solve this query in MySQL. Another possible approach is discussed in this SO post, but that's just ONE possible approach for this problem (and thus no reason to close this question as 'duplicate' since it clearly isn't...)

Does anybody know what route to take?

Community
  • 1
  • 1
patrick
  • 11,519
  • 8
  • 71
  • 80
  • I'm not sure I understand the question. Are you asking how to design the database, or are you asking how to convert the first table into the second table? – Barmar May 17 '14 at 16:12
  • If it's the design question, every competent SQL programmer will recommend that you use a many-to-many table, not put a list of countries in a column. – Barmar May 17 '14 at 16:13
  • convert. the Database is already there. It was created somewhere in 2001 :). the many-to-many approach is interesting and fast (that's why I considered that), but it would involve changing the whole backend of the site, many-to-many entries are not easy to maintain with something like PHPMyAdmin, the current database is... – patrick May 17 '14 at 16:13

0 Answers0