Need help with SQL Query

[RavenFrost]

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.

 

[vbkris]

why not use multiple where clauses?

e.g.:
pet = 'Dog' and pet='Lizard'



Known is handfull, Unknown is worldfull

 

[ca8msm]

You could use two EXISTS conditions e.g.

[COLOR=blue]CREATE[/color] [COLOR=blue]TABLE[/color] #PETS (id [COLOR=blue]int[/color] [COLOR=blue]IDENTITY[/color](1,1), Owner [COLOR=blue]varchar[/color](10), Amount [COLOR=blue]int[/color], Pet [COLOR=blue]varchar[/color](10))

[COLOR=blue]INSERT[/color] #PETS [COLOR=blue]VALUES[/color] ([COLOR=red]'sally'[/color], 1, [COLOR=red]'Dog'[/color])
[COLOR=blue]INSERT[/color] #PETS [COLOR=blue]VALUES[/color] ([COLOR=red]'mark'[/color], 2, [COLOR=red]'Cat'[/color])
[COLOR=blue]INSERT[/color] #PETS [COLOR=blue]VALUES[/color] ([COLOR=red]'sally'[/color], 2, [COLOR=red]'Cat'[/color])
[COLOR=blue]INSERT[/color] #PETS [COLOR=blue]VALUES[/color] ([COLOR=red]'chuck'[/color], 2, [COLOR=red]'Dog'[/color])
[COLOR=blue]INSERT[/color] #PETS [COLOR=blue]VALUES[/color] ([COLOR=red]'chuck'[/color], 1, [COLOR=red]'Cat'[/color])
[COLOR=blue]INSERT[/color] #PETS [COLOR=blue]VALUES[/color] ([COLOR=red]'mark'[/color], 0, [COLOR=red]'Dog'[/color])
[COLOR=blue]INSERT[/color] #PETS [COLOR=blue]VALUES[/color] ([COLOR=red]'sally'[/color], 2, [COLOR=red]'Lizard'[/color])

[COLOR=blue]SELECT[/color] [COLOR=#FF00FF]DISTINCT[/color] Owner
[COLOR=blue]FROM[/color] #PETS p
[COLOR=blue]WHERE[/color] EXISTS ([COLOR=blue]SELECT[/color] Owner [COLOR=blue]from[/color] #PETS p1 [COLOR=blue]WHERE[/color] Pet = [COLOR=red]'Lizard'[/color] AND p1.Owner = p.Owner)
AND EXISTS ([COLOR=blue]SELECT[/color] Owner [COLOR=blue]from[/color] #PETS p2 [COLOR=blue]WHERE[/color] Pet = [COLOR=red]'Dog'[/color] AND p2.Owner = p.Owner)
	
[COLOR=blue]DROP[/color] [COLOR=blue]TABLE[/color] #PETS

____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[PWise]

try

Select Owner 
From Pets
Where Case Pet when 'dog' then pet else '' end >''
and Case Pet when 'Lizard' then pet else '' end >''

 

[ca8msm]

why not use multiple where clauses?

e.g.:
pet = 'Dog' and pet='Lizard'

Because that would compare the value for each row and the value of pet will never equal 'Dog' and 'Lizard'.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[gmmastros]

You could also filter the list to ONLY contain Dog and Lizard and then use a having clause to only return owners with 2 records. Like this...

[COLOR=blue]CREATE[/color] [COLOR=blue]TABLE[/color] #PETS (id [COLOR=blue]int[/color] [COLOR=blue]IDENTITY[/color](1,1), Owner [COLOR=blue]varchar[/color](10), Amount [COLOR=blue]int[/color], Pet [COLOR=blue]varchar[/color](10))

[COLOR=blue]INSERT[/color] #PETS [COLOR=blue]VALUES[/color] ([COLOR=red]'sally'[/color], 1, [COLOR=red]'Dog'[/color])
[COLOR=blue]INSERT[/color] #PETS [COLOR=blue]VALUES[/color] ([COLOR=red]'mark'[/color], 2, [COLOR=red]'Cat'[/color])
[COLOR=blue]INSERT[/color] #PETS [COLOR=blue]VALUES[/color] ([COLOR=red]'sally'[/color], 2, [COLOR=red]'Cat'[/color])
[COLOR=blue]INSERT[/color] #PETS [COLOR=blue]VALUES[/color] ([COLOR=red]'chuck'[/color], 2, [COLOR=red]'Dog'[/color])
[COLOR=blue]INSERT[/color] #PETS [COLOR=blue]VALUES[/color] ([COLOR=red]'chuck'[/color], 1, [COLOR=red]'Cat'[/color])
[COLOR=blue]INSERT[/color] #PETS [COLOR=blue]VALUES[/color] ([COLOR=red]'mark'[/color], 0, [COLOR=red]'Dog'[/color])
[COLOR=blue]INSERT[/color] #PETS [COLOR=blue]VALUES[/color] ([COLOR=red]'sally'[/color], 2, [COLOR=red]'Lizard'[/color])

[COLOR=blue]Select[/color] 	Owner 
[COLOR=blue]From[/color] 	#Pets 
[COLOR=blue]Where[/color] 	Pet In ([COLOR=red]'Dog'[/color],[COLOR=red]'Lizard'[/color])
[COLOR=blue]Group[/color] [COLOR=blue]By[/color] Owner
[COLOR=blue]Having[/color] [COLOR=#FF00FF]Count[/color](*) > 1

[COLOR=blue]Drop[/color] [COLOR=blue]Table[/color] #Pets

-George

"the screen with the little boxes in the window." - Moron

 

[PWise]

gmmastros:
what will this return

CREATE TABLE #PETS (id int IDENTITY(1,1), Owner varchar(10), Amount int, Pet varchar(10))

INSERT #PETS VALUES ('sally', 1, 'Dog')
INSERT #PETS VALUES ('mark', 2, 'Cat')
INSERT #PETS VALUES ('sally', 2, 'Dog')
INSERT #PETS VALUES ('chuck', 2, 'Dog')
INSERT #PETS VALUES ('chuck', 1, 'Cat')
INSERT #PETS VALUES ('mark', 0, 'Dog')
INSERT #PETS VALUES ('sally', 2, 'Lizard')

Select     Owner 
From     #Pets 
Where     Pet In ('Dog','Lizard')
Group By Owner
Having Count(*) > 1

 

[gmmastros]

pwise,

It returns the data that RavenFrost is looking for. Specifically, it returns the owners that own a Dog AND a lizard.

-George

"the screen with the little boxes in the window." - Moron

 

[PWise]

if there are for one owner two records for dog and no records for lizard you query will return that owner

 

[gmmastros]

pwise,

I see what you mean now. I assumed that since there is an Amount column, that there wouldn't be multiple records for an owner/pet combination. If it is possible to have those kind of duplicates, then this query would work.

[COLOR=blue]CREATE[/color] [COLOR=blue]TABLE[/color] #PETS (id [COLOR=blue]int[/color] [COLOR=blue]IDENTITY[/color](1,1), Owner [COLOR=blue]varchar[/color](10), Amount [COLOR=blue]int[/color], Pet [COLOR=blue]varchar[/color](10))

[COLOR=blue]INSERT[/color] #PETS [COLOR=blue]VALUES[/color] ([COLOR=red]'sally'[/color], 1, [COLOR=red]'Dog'[/color])
[COLOR=blue]INSERT[/color] #PETS [COLOR=blue]VALUES[/color] ([COLOR=red]'sally'[/color], 2, [COLOR=red]'Dog'[/color])
[COLOR=blue]INSERT[/color] #PETS [COLOR=blue]VALUES[/color] ([COLOR=red]'chuck'[/color], 2, [COLOR=red]'Dog'[/color])
[COLOR=blue]INSERT[/color] #PETS [COLOR=blue]VALUES[/color] ([COLOR=red]'chuck'[/color], 2, [COLOR=red]'Dog'[/color])
[COLOR=blue]INSERT[/color] #PETS [COLOR=blue]VALUES[/color] ([COLOR=red]'chuck'[/color], 1, [COLOR=red]'Cat'[/color])
[COLOR=blue]INSERT[/color] #PETS [COLOR=blue]VALUES[/color] ([COLOR=red]'sally'[/color], 2, [COLOR=red]'Lizard'[/color])

[COLOR=blue]Select[/color] Owner
[COLOR=blue]From[/color]   (
       [COLOR=blue]Select[/color] [COLOR=#FF00FF]Distinct[/color] Owner, Pet
       [COLOR=blue]From[/color]   #Pets
       [COLOR=blue]Where[/color]  Pet In ([COLOR=red]'Dog'[/color], [COLOR=red]'Lizard'[/color])
       ) [COLOR=blue]As[/color] A
[COLOR=blue]Group[/color] [COLOR=blue]By[/color] Owner
[COLOR=blue]Having[/color] [COLOR=#FF00FF]Count[/color](*) > 1

[COLOR=blue]Drop[/color] [COLOR=blue]Table[/color] #Pets

-George

"the screen with the little boxes in the window." - Moron

 

[ca8msm]

pwise, your query doesn't return any records from the sample data?


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[bjb123]

I think you can also do this with a self join, using the table created above

Select Distinct p1.Owner
FROM #PETS p1, #PETS p2
WHERE p1.Owner = p2.Owner
AND p1.Pet = 'Lizard'
AND p2.Pet = 'Dog'

 

[bjb123]

And if you need the amount to be > 0 then just add that criteria to the where condition

Select Distinct p1.Owner
FROM #PETS p1, #PETS p2
WHERE p1.Owner = p2.Owner
AND p1.Pet = 'Lizard' and p1.Amount > 0
AND p2.Pet = 'Dog' and p2.Amount > 0

 

[vbkris]

>>Because that would compare the value for each row and the value of pet will never equal 'Dog' and 'Lizard'

yup, you are correct, did not think much before posting that one (late in the day)...

Known is handfull, Unknown is worldfull