Finding Records With Only Matching Values

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

Finding Records With Only Matching Values

Author

Topic

LemonDrops
Starting Member

2 Posts

Posted - 2011-03-15 : 23:51:41

Sorry a lousy subject description.
I have a table with three columns
Column one is unique values (Animal_Id)
Column two is one of two values (Animal_Type) pig or cow
Column three is a non-unique value, numeric (Barn_ID) 100, 200, 300, etc

Column three's value is the key in another table that I don't think is relevant here (Barn_Location)

There are no nulls.
Some barns have only pigs, some only cows, and some both pigs and cows.
I want to find out which barns have only pigs.
The table looks like this:
Animal_id Animal_Type Barn_ID
1 cow 300
2 cow 200
3 pig 300
4 cow 200
5 cow 100
6 pig 400
7 pig 300
8 cow 200
9 pig 400
10 pig 200
11 cow 300
12 cow 200
13 pig 300
14 cow 200
15 cow 100
16 pig 400
17 cow 300
18 cow 200
19 pig 400
20 pig 200
21 cow 300
22 cow 200
23 pig 300
24 cow 200
25 cow 100
26 pig 400
27 cow 300
28 cow 200
29 pig 400

IF I do
select count(animal_type), barn_id, animal_type
from animals
group by barn_id, animal_type;

That obviously isn't the answer since it doesn't sort out only pigs. And if I try adding "where animal-type='pig'" THAT isn't right either because the results include barns that also have cows.

And what would have been a more accurate subject line description?

And - it reads like a homework assignment, but it isn't. I'm self learning on the job.
Thanks

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-03-16 : 01:44:35

One way:

Create table #Test
(Animal_id int,
Animal_Type varchar(50),
Barn_ID int
)

truncate table #Test

Insert into #Test
select 1 ,'cow', 300 union all
select 2 ,'cow', 200 union all
select 3 ,'pig', 300 union all
select 4 ,'cow', 200 union all
select 5 ,'cow', 100 union all
select 6 ,'pig', 400 union all
select 7 ,'pig', 300 union all
select 8 ,'cow', 200 union all
select 9 ,'pig', 400 union all
select 10 ,'pig', 200 union all
select 11 ,'cow', 300 union all
select 12 ,'cow', 200 union all
select 13 ,'pig', 500 union all
select 14 ,'zebra', 600 union all
select 14 ,'pig', 600

Select Barn_id from #Test
group by Barn_id
having Count(distinct Animal_type) = 1 and max(Animal_type)='pig'

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-03-16 : 17:24:12

SELECT barn_id
FROM Test
GROUP BY barn_id
HAVING MAX(animal_type) = MIN(animal_type)
AND MAX(animal_type) = 'pig';

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-03-16 : 18:40:50

[code]
select distinct barn_id --You want barn_ids, no duplicates
from animals pigs
where animalType = 'pig' --these are pigs, you display their barns
and not exists(select *
from animals cows
where cows.barn_id = pigs.barn_id
)
--you do not want to include barns that are linked to cows too
[/code]

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

LemonDrops
Starting Member

2 Posts

Posted - 2011-03-16 : 20:41:38

Interesting variations in solutions. Thanks for solving my problem! Got me to look up "not exists" too.

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-03-18 : 02:31:50

quote:

Originally posted by mmarovic


select distinct barn_id --You want barn_ids, no duplicates
from animals pigs
where animalType = 'pig' --these are pigs, you display their barns
  and not exists(select *
                   from animals cows
                   where cows.barn_id = pigs.barn_id
                )
--you do not want to include barns that are linked to cows too

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

Sorry, there was an error in code, the correct one is:


select distinct barn_id --You want barn_ids, no duplicates
from animals pigs
where animalType = 'pig' --these are pigs, you display their barns
  and not exists(select *
                   from animals cows
                   where cows.barn_id = pigs.barn_id
                     and animalType = 'cow'
                )
--you do not want to include barns that are linked to cows too

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources