TSQl problem

[habneh]

Here is my problem

I have a table say

trialId country
1 USA
1 canada
1 Jamaica
2 USA
3 Afganistan
4 India
4 S. Africa

I want to get trials conducted in US but nowhere else

resultset should look like
trialId country
2 USA

and the opposite Trials elsewhere but not in US

resultset
trialID country
3 Afganistan
4 India
4 S. Africa

how can I do it easily

Thanks

 

[AlexCuse]

Using NOT IN. Here's an example:

[COLOR=green]--test data
[/color][COLOR=blue]declare[/color] @trial [COLOR=blue]table[/color] (trialID [COLOR=blue]int[/color], country [COLOR=blue]varchar[/color](30))

[COLOR=blue]insert[/color] @trial
[COLOR=blue]select[/color] 1, [COLOR=red]'USA'[/color]
union all [COLOR=blue]select[/color] 1, [COLOR=red]'Canada'[/color]
union all [COLOR=blue]select[/color] 1, [COLOR=red]'Jamaica'[/color]
union all [COLOR=blue]select[/color] 2, [COLOR=red]'USA'[/color]
union all [COLOR=blue]select[/color] 3, [COLOR=red]'Afghanistan'[/color]
union all [COLOR=blue]select[/color] 4, [COLOR=red]'India'[/color]
union all [COLOR=blue]select[/color] 4, [COLOR=red]'S. Africa'[/color]

[COLOR=green]--NOT USA ONLY
[/color][COLOR=blue]select[/color] * [COLOR=blue]from[/color] @trial 
[COLOR=blue]where[/color] trialID NOT IN ([COLOR=blue]select[/color] trialID [COLOR=blue]from[/color] @trial [COLOR=blue]where[/color] country = [COLOR=red]'USA'[/color])

[COLOR=green]--USA ONLY
[/color][COLOR=blue]select[/color] * [COLOR=blue]from[/color] @trial 
[COLOR=blue]where[/color] trialID NOT IN ([COLOR=blue]select[/color] trialID [COLOR=blue]from[/color] @trial [COLOR=blue]where[/color] country <> [COLOR=red]'USA'[/color])

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

Or, if you'd rather use joins:

[COLOR=green]--NOT USA ONLY
[/color][COLOR=blue]select[/color] a.* [COLOR=blue]from[/color] @trial a
[COLOR=#FF00FF]left[/color] [COLOR=blue]join[/color]
(
[COLOR=blue]select[/color] trialID [COLOR=blue]from[/color] @trial [COLOR=blue]where[/color] country = [COLOR=red]'USA'[/color]
) b
[COLOR=blue]on[/color] a.trialID = b.trialID
[COLOR=blue]where[/color] b.trialID [COLOR=blue]is[/color] null

[COLOR=green]--USA ONLY
[/color][COLOR=blue]select[/color] a.* [COLOR=blue]from[/color] @trial a
[COLOR=#FF00FF]left[/color] [COLOR=blue]join[/color]
( 
[COLOR=blue]select[/color] trialID [COLOR=blue]from[/color] @trial [COLOR=blue]where[/color] country <> [COLOR=red]'USA'[/color]
) b
[COLOR=blue]on[/color] a.trialID = b.trialID
[COLOR=blue]where[/color] b.trialID [COLOR=blue]is[/color] null

Ignorance of certain subjects is a great part of wisdom

 

[habneh]

Thanks Alex, That helps alot

 

[AlexCuse]

No problem, glad you got it working :-D

Ignorance of certain subjects is a great part of wisdom

 

[habneh]

Alex,
here is another one

say I have table tbl

trialId restriction flag
1 domestic yes
1 foreign yes
2 domestic no
2 foreign yes
3 domestic yes
3 foreign no
4 domestic yes
4 foreign yes
5 domestic yes
5 foreign NO

I want to select those trials who are conducted only domestically

the record set should look like

trialId restriction flag
3 domestic yes
5 domestic yes

thanks

 

[AlexCuse]

And why are 1, 2, & 4 not showing in your desired result?

Oh wait, I think I understand.

How about this?

select a.trialID
, a.restriction
, a.flag
from 
(
select * from tbl 
where restriction = 'domestic' 
and flag = 'yes'
) a
inner join
(
select * from tbl
where restriction = 'foreign'
and flag = 'no'
) b
on a.trialID = b.trialID

or this, not sure which would perform better:

select a.trialID
, a.restriction
, a.flag
from 
tbl a
inner join
(
select * from tbl
where restriction = 'foreign'
and flag = 'no'
) b
on a.trialID = b.trialID
and a.restriction = 'domestic'
and a.flag = 'yes'

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Chopstik]

habneh, if you have another question, you may do well to start another thread for it.

Also, have you tried to code for this yourself to see what you can come up with? If you then have a more specific question regarding your code, it demonstrates that you are using this site as it is intended. The professionals on this site are just that: professionals. We try to figure out answers for ourselves and are not averse to assisting others provided it does interfere with our regular jobs. But we may be more inclined to assist those who demonstrate that they are willing to first do things themselves before relying on others here to do their work for them. We are also more inclined to help those who show a willingness to help others as well.

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[habneh]

That was perfect

Thank you very much

 

[SQLDenis]

I guess you don't know the meaning of
SELECT (*)

http://www.tek-tips.com/userinfo.cfm?member=habneh

Where * = purple star of course

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions