Query Writing Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query Writing Help

Author

Topic

AdiI
Starting Member

31 Posts

Posted - 2010-05-12 : 02:12:20

plz help to write a query. i have table let say 2 column in it city , country here is my example
city country
LHR PAK
KAR PAK
ISB PAK
LHR USA
KAR USA
ISB USA
LHR GERMANY

Now i want name of the country where LHR, KAR exist? how would write sql for it. i have tried group by or having but it work row level?
any Idea or suggestion

adeel

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-12 : 02:24:23

What should be the output regarding to your example data?

No, you're never too old to Yak'n'Roll if you're too young to die.

sql-programmers
Posting Yak Master

190 Posts

Posted - 2010-05-12 : 02:26:55

I assume that you need output

LHR PAK
KAR PAK
LHR USA
KAR USA
LHR GERMANY

If this is the case use the scripts,

create table #temp(city varchar(10),country varchar(10))
insert into #temp values('LHR', 'PAK')
insert into #temp values('KAR', 'PAK')
insert into #temp values('ISB', 'PAK')
insert into #temp values('LHR', 'USA')
insert into #temp values('KAR', 'USA')
insert into #temp values('ISB', 'USA')
insert into #temp values('LHR', 'GERMANY')

select * from #temp where city in ('LHR','KAR')

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

AdiI
Starting Member

31 Posts

Posted - 2010-05-12 : 03:14:35

thanks for help.But your output include Germany as well. i have required only those record who have BOTH LHR and KAR. Hope understand what i' looking for. In this example Germany would not be the part of Output

adeel

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-12 : 03:22:43

not the best but it works:


create table #temp(city varchar(10),country varchar(10))
insert into #temp values('LHR', 'PAK')
insert into #temp values('KAR', 'PAK')
insert into #temp values('ISB', 'PAK')
insert into #temp values('LHR', 'USA')
insert into #temp values('KAR', 'USA')
insert into #temp values('ISB', 'USA')
insert into #temp values('LHR', 'GERMANY')

select * from #temp t1
where t1.city = 'LHR'
  and exists (select * from #temp t2 where t2.city='KAR' and t2.country = t1.country)
UNION
select * from #temp t1
where t1.city = 'KAR'
  and exists (select * from #temp t2 where t2.city='LHR' and t2.country = t1.country)

drop table #temp

No, you're never too old to Yak'n'Roll if you're too young to die.

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-12 : 04:25:02

Try this:


SELECT country
  FROM YourTable AS T
 WHERE city = 'LHR'
    OR city = 'KAR'
 GROUP BY country
HAVING COUNT(DISTINCT city) = 2

soaphope
Starting Member

13 Posts

Posted - 2011-02-12 : 20:48:48

How about:

WITH T AS (
SELECT 'LHR' as city, 'PAK' as country UNION ALL
SELECT 'KAR', 'PAK' UNION ALL
SELECT 'ISB', 'PAK' UNION ALL
SELECT 'LHR', 'USA' UNION ALL
SELECT 'KAR', 'USA' UNION ALL
SELECT 'ISB', 'USA' UNION ALL
SELECT 'LHR', 'GERMANY'
)
SELECT country from T WHERE city = 'KAR' INTERSECT
SELECT country from T WHERE city = 'LHR'

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-12 : 23:59:58

quote:
Originally posted by AdiI

plz help to write a query. i have table let say 2 column in it city , country here is my example
city country
LHR PAK
KAR PAK
ISB PAK
LHR USA
KAR USA
ISB USA
LHR GERMANY

Now i want name of the country where LHR, KAR exist? how would write sql for it. i have tried group by or having but it work row level?
any Idea or suggestion

adeel

Select Country From TableName Where city in ('LHR','KAR');

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-13 : 00:00:36

quote:
Originally posted by AdiI

plz help to write a query. i have table let say 2 column in it city , country here is my example
city country
LHR PAK
KAR PAK
ISB PAK
LHR USA
KAR USA
ISB USA
LHR GERMANY

Now i want name of the country where LHR, KAR exist? how would write sql for it. i have tried group by or having but it work row level?
any Idea or suggestion

adeel

Select DISTINCT Country From TableName Where city in ('LHR','KAR');

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources