how to exclude in mysql

jansky

hi.

how can i exclude a certain row in my tables

eg:

Id, Country
1, US
2, US-California
3, US-New York
4, US-Guam
5, Canada
6. Canada-Ontario
7. Japan
8. Japan-OKinawa

and the display should be
US,Canada,Japan

I can exclude by using PHP code, but How can that be done in SQL statement?

NogDog

Well, it looks like one solution would be better normalization so that you don't have states/territories in your "country" column. 😉

Otherwise, if I'm correctly interpreting what you want to do, you could do something like:

. . . WHERE country NOT LIKE '&#37;-%'

However, what happens if you have the country 'Timor-Leste' in your database?

jansky

thanks..

i just did it..

indeed its not like '%-%'

Bonesnap

I agree with NogDog; better database design would make this a non-issue. Your country table should only have countries. Your territory table would then have an ID that links it to its respective country (AKA, foreign key).