Need help with challenging MySQL query!
Development and Programming
I’m sure someone with twice my brain power can help me out here…
I have five columns called Region1, Region2, all the way to Region5. I have five more columns called Location1, Location2 and so on, which correspond to the same numbered Region column.
I’m trying to write a SELECT query which returns a list of discrete values from all five location columns, but only if the corresponding region column contains a certain value.
For example, I might want to fetch a list of all the discrete locations where the corresponding Region value is ‘Southern France’, even though Southern France could exist in one or more of those Region columns.
Anyone?
To be honest, it sounds like you need to look a bit closer at your database design. Could you give more of an idea of what is in your database table, and why you need to have region1..5 corresponding to location1..5 as columns?
Each row in your database should contain information relating only to one particular thing (the primary key). Different (non-primary key) columns in the table should never hold information about other columns, for example having region1 related to location1 and region2 related to region2 inside your table would be considered bad design, it would be most likely better to have a separate table linking a region and location to your original table (and you could use php code to ensure no more than 5 get entered if that is what you wish). But I don’t want to second guess your design, more information would be helpful.
If you have a good reason for the existing layout, then could you do something like ‘select * from tablename where region1 == ‘southern france’ OR region2 == ‘southern france’ OR region3… etc?