Problem with Query construction

[ramegharaj]

Hi All,

I've a table name Countries with two fields as ID, and CountryName.
Where ID is foreign key(ie., may have duplications) and the country name can be repeated as many times as it needed.

for example the scenario is

ID CountryName

1 India
1 US
1 UK
1 Japan
2 Italy
2 UK
2 Brazil
2 Australia
2 India
3 UK

consider the above scenario where I need to get the ID's for which the CountryName India and also UK ( that means I should get the ID 1 and 2 as an output)

SELECT * FROM Countries WHERE CountryName='India' AND CountryName = 'UK'.

I know the above query won't retrieve even a single record. (I can't put OR cause it checks either of the countries).

Like that I've 'n' number of records in my table with 247 different countries.

Please help me to get it resolved.

Thanks in advance.

Ram Megharaj

[Ole Raptor]

so you have 247 different countries that can be entered into the same table multiple times. in other words you could have millions of records in this single table?
anyway sorry about rambling just didnt understand the table. the select statement should work. is it giving you any errors?

[ramegharaj]

The Select statement doesn't retrieve any records there. Any how I got the answer. We have to use INTERSECT keyword.

SELECT ID FROM Countries WHERE CountryName='India'
INTERSECT
SELECT ID FROM Countries WHERE CountryName='UK';