find output using IN clause

Question

Post reply

find output using IN clause

ashuthinks

SSCommitted

Points: 1698
More actions
September 28, 2011 at 3:15 am

#390492

i have table like
bbc(name, region, area, population, gdp)
want to select
List the name and region of countries in the regions containing 'India', 'Iran'.
i tried like
select name , region from bbc where name IN (
select name from bbc where region IN ('India','Iran') )
but something it wrong plz help thank you

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

ashuthinks (9/28/2011)
something it wrong

Care to be more specific?

Your query will return only rows where region is equal to 'India' or 'Iran'. If you want rows that contain certain strings of characters, you will need to use the LIKE operator with wildcards.

John

ashuthinks SSCommitted Points: 1698 More actions · Answer 2

Hi i just need to list out the the name and region of countries in the regions containing 'India', 'Iran'.

from table bbc(name, region, area, population, gdp)

name region area population gdp

india asia 50 123 2

iran asia 20 111 3

using sub query

i need to select name and region of the countries in the regions containing 'India', 'Iran'.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

First, select the region(s) where name is India or Iran. Then join back to the table to get the other names with the same region(s). If you provide DDL in the form of a CREATE TABLE statement and sample data in the form of INSERT statements, we'd be able to help you with a tested solution.

John

MuraliKrishnan1980 SSCrazy Points: 2497 More actions · Answer 4

If you are pulling the records from the same table, i do not see the reason why you are going for the sub query. You can pull the records without using the sub query.

palash.gorai Mr or Mrs. 500 Points: 567 More actions · Answer 5

Hi,

What i understood is that you need to retrieve the data from bbc tbl where regions are India and Iran.

If i am right, then try this one:-

SELECT *

FROM BBC

WHERE REGIONS IN ('INDIA','IRAN')

Regards

Palash Gorai

ashuthinks SSCommitted Points: 1698 More actions · Answer 6

ashuthinks

SSCommitted

Points: 1698

September 28, 2011 at 4:11 am

#1387637

Thanks all 🙂

Ratheesh@SQL Grasshopper Points: 19 More actions · Answer 7

I think you have typed the wrong column in the where clause 🙂

I assume this is the sample data from your table, and you want to filter the records which contains india and iran.

name region area population gdp

india asia 50 123 2

iran asia 20 111 3

Your query will not return data because the region column doesn't contains India / Iran..

Your query should be

select name , region from bbc where [name] in ('India','Iran')

Charmer SSChampion Points: 14903 More actions · Answer 8

yes, Ratheesh is correct...

may be you select wrong column...

just use select name,region from bbc where name in ('india', 'iran')

this is more enough to select the particular records of this region..

if you are really in need of some filters, then explain briefly to help us to help you...

Thanks,
Charmer

palash.gorai Mr or Mrs. 500 Points: 567 More actions · Answer 9

Correct, it should be Name not REGION.

regards

Palash Gorai

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 10

prakash 67108 (9/28/2011)
yes, Ratheesh is correct...
may be you select wrong column...
just use select name,region from bbc where name in ('india', 'iran')

No, because this doesn't return the other countries in the same region(s), which is the requirement.

John

ghanshyam.kundu Hall of Fame Points: 3337 More actions · Answer 11

>>want to select

List the name and region of countries in the regions containing 'India', 'Iran'.<<

as per ur requirement you can try this one

create table #bbc(name nvarchar(20), region nvarchar(20), area nvarchar(20), population bigint, gdp bigint)

insert into #bbc values ('india','aisa','20000',1200000,8)

insert into #bbc values ('china','aisa','20000',1200000,8)

insert into #bbc values ('iran','aisa','20000',1200000,8)

insert into #bbc values ('pakistan','aisa','20000',1200000,8)

insert into #bbc values ('london','europe','20000',1200000,8)

insert into #bbc values ('brazil','america','20000',1200000,8)

select name, region from #bbc where region in (select region from #bbc where name in('india','iran'))

we can achieve it through self join also.

ghanshyam.kundu Hall of Fame Points: 3337 More actions · Answer 12

this is the o/p of the query

nameregionareapopulationgdp

indiaaisa2000012000008

chinaaisa2000012000008

iranaisa2000012000008

pakistanaisa2000012000008

do post what is your desired result.

Charmer SSChampion Points: 14903 More actions · Answer 13

yeh...he needs only india and iran...why should we go for other countries..?

Thanks,
Charmer

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 14

prakash 67108 (9/28/2011)
he needs only india and iran...why should we go for other countries..?

ashuthinks (9/28/2011)
i need to select name and region of the countries in the regions containing 'India', 'Iran'.

Using the sample data posted by ghanshyam.kundu, we see that the region containing India and Iran is Asia. We then see that the countries in that region are Pakistan, China, India and Iran. That's why.

John