Advanced query technique 3

[jimmyshoes]

I have a fruit table as follows

UserID Type

1 Orange
2 Apple
1 Pineapple
2 Kiwi
2 Pineapple

I am trying to create a query which for example returns the userid of anyone who has both an Orange AND a Pineapple (in this case User ID 1)

I have been experiementing with
select UserID from fruit
where type = 'Orange' and type = 'Pineapple'

but this yields no results

 

[bborissov]

SELECT YourTable.*
FROM YourTable
INNER JOIN YourTable Orange 
           ON YourTable.Id = Orange.Id AND
              Orange.Type = 'Orange' 
INNER JOIN YourTable Pineapple
           ON YourTable.Id = Pineapple.Id AND
              Orange.Type = 'Pineapple'

NOT TESTED!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[jimmyshoes]

Thanks for your help. It works well ( one typo, final line should be Pineapple.Type = 'Pineapple' )

I want to test which fruit a user has based on a table of possible values

Table Test
Tester

Orange
Pineapple

I have come up with the following

However, the results are not reliable. Also, if I have say 30 possible fruits to test then I am going to need 30 seperate INNER JOINS. Even if I can get this solution to work, is it bad design to have so many inner joins. If it is, can you think of an alternative approach?

SELECT Distinct (YourTable.Id)
FROM YourTable
INNER JOIN YourTable Orange
           ON YourTable.Id = Orange.Id AND
              Orange.Type IN (select tester from test)
INNER JOIN YourTable Pineapple
           ON YourTable.Id = Pineapple.Id AND
              Pineapple.Type IN (select tester from test)
INNER JOIN YourTable Mango
           ON YourTable.Id = Mango.Id AND
              Mango.Type IN (select tester from test)

 

[gmmastros]

Here's another way. It will probably perform better, and will likely be easier to maintain. I created a table variable with your sample data so you can copy/paste this to a query window to see how it works.

Declare @Temp Table(UserId Int, Type VarChar(20))

Insert Into @Temp Values(1,'Orange')
Insert Into @Temp Values(2,'Apple')
Insert Into @Temp Values(1,'Pineapple')
Insert Into @Temp Values(2,'Kiwi')
Insert Into @Temp Values(2,'Pineapple')

-- Orange and Pineapple
Select UserId
From   @Temp
Group By UserId
Having 2 = Min(Case When Type = 'Orange' Then 1 End)
           + Min(Case When Type = 'Pineapple' Then 1 End)

-- Kiwi and Pinapple
Select UserId
From   @Temp
Group By UserId
Having 2 = Min(Case When Type = 'Kiwi' Then 1 End)
           + Min(Case When Type = 'Pineapple' Then 1 End)

-- Kiwi, Apple and Pineapple
Select UserId
From   @Temp
Group By UserId
Having [!]3[/!] = Min(Case When Type = 'Kiwi' Then 1 End)
           + Min(Case When Type = 'Pineapple' Then 1 End)
           + Min(Case When Type = 'Apple' Then 1 End)

With this method, all you need to do is change the Having clause. Make sure you change the number to represent the number of conditions and add another Min(....) part.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

If you only need to find the UserID who has Mango, Kivi, Orange, then

select UserID, count(distinct(Type)) as CountFruits 
from Fruit where Type in ('Mango','Kivi','Orange') 
group by UserID having count(distinct(Type)) = 3 -- 3 different types of fruits

You may also take a look at this blog, that talks about similar, but a bit more complex problem

How to search for all words inclusive without using Full Text search

PluralSight Learning Library

 

[jimmyshoes]

Thanks for your help.

After initial testing this query is looking good

Select UserID
From Fruit Where Type in (Select Tester from Test)
Group by UserID having Count(Distinct(Type)) = (Select Count(Tester) from Test)

It strikes me that this would be a good way to index items that occur in multiple categories. For example, a booktore: if you had a book that fitted both the humour and travel categories then you could pass in 'humour' 'travel' or 'humour','travel' and it would show up