quite a strange select... - 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

SQL Server 2000 Forums

Transact-SQL (2000)

quite a strange select...

Author

Topic

Finarfin
Starting Member

28 Posts

Posted - 2004-06-01 : 06:10:36

Hi all,
I have a problem to create my query, may you could help me.

There is one table:
table1 : code, country
1 code can have several countries

I want to select * from table1 where code is only associated to ONE country.

This must be something like that:
select distinct code, country from table1
where(count(select distinct country from table1
where code = code)=1)

...but it is of course not

Thank you all

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-06-01 : 06:27:51

[code]
select *
from yourTable
where exists (select 1
from yourTable as a
where a.code= yourTable.code
group by code having count(*) = 1)
[/code]

_________________________________________________________
The cradle of civilisation will defeat the new monsters of the world.

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-06-01 : 06:30:04

try :

select code,country,count(*)
from table1
group by code,country
having count(*) = 1

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-06-01 : 06:31:19

Hmm - go with Amethysiums - he post while I was typing, and is neater, it would seem

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Finarfin
Starting Member

28 Posts

Posted - 2004-06-01 : 06:43:20

Thank you both of you,
But the queries you sent me aren't working. I think I forgot some informations when asking the question.
The table I have is like this:

code country
01 USA
01 FRANCE
02 USA
03 GERMANY
03 USA
04 FRANCE
05 ...
... ...

The query will return
02 USA
04 FRANCE

Thank you all

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-06-01 : 06:48:43

select max(code) as code, country
from yourtable
group by country

Duane.

ooops! after Wanderers analysis I realised that I was way off.
I Totally misunderstood the question - I realised this once I read the entire thread as opposed to the last post.

mr_mist
Grunnio

1870 Posts

Posted - 2004-06-01 : 07:21:57

create table #moo (code int, con varchar(10))
insert into #moo values (1,'usa')
insert into #moo values (1,'france')
insert into #moo values (2,'usa')
insert into #moo values (3,'germany')
insert into #moo values (3,'usa')
insert into #moo values (4,'france')

select max(code) as code, con
from #moo
group by con

select a.code, a.con
from #moo a
left outer join #moo b
on a.code = b.code
and a.con != b.con
where b.code is null

-------
Moo. :)

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-06-01 : 08:47:04

Well, I ran Ames's (sorry for the shortening) after Mist's temp create table, and it give the same result as Mist's:

code con
----------- ----------
2 usa
4 france

which certainly seems to be the solution, given the test case.

It will differ in a real environment, but on a very small table (Mist's example), Ames's solution SEEMs marginally better from an access path point of view: 0.0755 cost vs 0.0756

Finarfin : it that doesn't help, please give the specific table structure - that might give us a clue.

Cheers

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-06-01 : 08:52:32

Wanderer, thanks for the analysis.

I've always encourged myself to use the EXISTS operator as it always seems to score on performance Vs other ways of doing the same thing.

oh and nice to see you again Mr mist

_________________________________________________________
The cradle of civilisation will defeat the new monsters of the world.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-06-01 : 09:04:46

How would you know which country to return for code 01 and 03???

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-06-01 : 09:08:25

LOL, RickD I asked myself the same question but I guess he got the answer he's looking for. I'm sure his design deals with this though.

_________________________________________________________
The cradle of civilisation will defeat the new monsters of the world.

Finarfin
Starting Member

28 Posts

Posted - 2004-06-01 : 10:05:34

I will not go away without thanking you, that is just that I was in meeting...
In fact, to answer your questions, I didn't gave you the whole table (there are 10 columns with a 4 columns primary key combination).
I tried all the solutions you gave me and the only one working is mist's, I don't know why but Ame's one isn't giving me the good results with the whole table.

But I don't think my problems are over because I need to add other conditions to that query...
Be sure I will keep you in touch

Thank you all

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-06-01 : 11:26:38

Good luck Finarfin! say hi to Paris for me (if you live there).. haven't been for 2 years.

_________________________________________________________
The cradle of civilisation will defeat the new monsters of the world.

Finarfin
Starting Member

28 Posts

Posted - 2004-06-01 : 11:32:26

Thank you Ame, I will do my best!
And I'll go to the Tour Eiffel to say hello to Paris for you

Thank you all

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources