I'm on EE2.10.1, and trying to grab unique countries from a channel that has one field for "country name" and one field for "country code". So I've got the following query:

{exp:query sql="SELECT DISTINCT field_id_47 AS country_code, field_id_46 AS country FROM exp_channel_data WHERE channel_id='4' ORDER BY field_id_46 ASC"}
    <option value="{country_code}">{country}</option>

... which works great, except: I need to exclude expired entries (but include future entries). I think it probably involves doing a JOIN on exp_channel_titles, but my SQL isn't really up to it!

Anyone know what the correct query should be? Thanks loads!

1 Answer 1


try something like this:

d.field_id_47 AS country_code, 
d.field_id_46 AS country 
FROM exp_channel_data d
LEFT JOIN exp_channel_titles t ON d.channel_id = t.channel_id
WHERE t.channel_id='4' 
AND ( t.expiration_date = 0 OR t.expiration_date >= UNIX_TIMESTAMP() )
ORDER BY d.field_id_46 ASC
  • Thanks for posting this – unfortunately it didn't seem to work – it still outputs entries that have expired... And just to make things more complicated: I realise now that I also need to restrict it to entries that are related to a specific entry in another channel. So something along the lines of "WHERE d.field_id_36={some_entry_id}" – except that I see relationships are in their own db table too! Do you think this is possible? Thanks so much for your help. Jan 12, 2016 at 16:02
  • Why don't you use the channel entries tag pair and all that comes with it, just do some housekeeping and get rid of the duplicates, then you can use the relationships as well.
    – Jim Wyse
    Jan 12, 2016 at 18:54
  • Yes that's probably a good plan – I was hoping to come up with a super efficient query but it's probably overly complicated! Thanks for your help, much appreciated. Jan 12, 2016 at 20:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.