Query exp_channel_data but exclude expired entries

Question

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>
{/exp:query}

... 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!

Answer 1

try something like this:

SELECT DISTINCT 
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