Need help with SQL query - 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 2005 Forums

Transact-SQL (2005)

Need help with SQL query

Author

Topic

ravenfrost
Starting Member

1 Post

Posted - 2007-08-09 : 09:01:13

Hello, if you look at the image below you can see an example table. Lets say I want to know which owners own a lizard AND a dog, not which owners own a lizard OR a dog. Therefore the IN command wouldn't work because it performs an OR. Lets call the table test. Select Owner from test Where Pet... I don't how the rest would go. I'm in a stump here. Any suggestions would help. Thanks.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-08-09 : 09:28:18

If you have a strong PK in this resultset of Owner/Per, then it is simply:

select Owner
from Yourtable
where Pet in ('Dog','Cat')
having count(*)=2

If the PK is not owner/Pet, then you need to use COUNT DISTINCT, which is a little less efficient:

select Owner
from Yourtable
where Pet in ('Dog','Cat')
having count(distinct Pet)=2

- Jeff
http://weblogs.sqlteam.com/JeffS

nr
SQLTeam MVY

12543 Posts

Posted - 2007-08-09 : 09:28:23

select owner
from tbl
where pet in ('lizard','dog')
group by owner
having count(distinct pet) = 2

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-09 : 09:29:45

assuming sally of id 4 is the same sally as ids 6 & 10 and mark owns 0 dogs

declare @tbl table (id int,owner varchar(10),[count] int,pet varchar(10))

insert into @tbl

select 4,'sally',1,'dog'
union
select 6,'sally',2,'cat'
union
select 10,'sally',2,'lizard'

select a.owner,a.pet,b.pet
from @tbl a
inner join @tbl b
on
a.owner = b.owner

where a.pet = 'lizard' and b.pet = 'dog'
and a.[count]> 0 and b.[count] > 0

Jim

nr
SQLTeam MVY

12543 Posts

Posted - 2007-08-09 : 10:06:11

didn't notice the count.

select owner
from tbl
where pet in ('lizard','dog')
and [count] <> 0
group by owner
having count(distinct pet) = 2

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-08-09 : 12:21:24

oops -- forgot the GROUP BY Owner in my answer(s), missed the count. too. see Nigel's answer, same thing as what I tried to write.

jimf -- no need for joins here. a GROUP BY is all you need, it is more efficient and also lets you find any N matches that you need.

more here:

http://weblogs.sqlteam.com/jeffs/archive/2007/06/12/60230.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-09 : 13:39:09

Yes, the GROUP BY is the way to go. I like doing things the hard way for the same reason I like hitting my head with a hammer. nr's returns the correct result, as best I understand what ravenfrost wanted, there were a lot of assumptions to be made,

Jim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources