I am new to DBs and have started with something simple...a cookbook
database. I have a table of 'recipes', with information about the
recipe, as well as a key index for each recipe. Next, I have a table
of 'ingredients', with information about the ingredient, as well as a
key index for reach ingredient. Finally, I have a relationship table,
which associates the ingredients with the recipes. It looks something
like:

Recipe # Ingredient # Quantity
1 14 2 cups
1 22 3 tbsp
2 7 4 lbs
etc

This is working really well when I just need to get the information
about a recipe, a simple "SELECT....WHERE Recipe# = ##" returns all
that I need. However, I would like to extend this database so that
when I give it a list of 'ingredients', it will show me all 'recipes' I
can make with the given ingredients. For instance, in the top table if
I have ingredients 10, 14, and 22, it should tell me I can make recipe
#1.

Is there an easy way to do this with the table setup I currently have?
If not, does anyone have a suggestion as to how I can proceed.

Sorry if this is a dumb newbie question, but the only solutions I can
come up with will take way too much processing time and are entirely
inefficient.

Hodge.Nate (AT) gmail (DOT) com wrote:
Given your design, it will likely require some procedural code (ie,
something outside of straight SQL SELECT) Although Now that I think
about it, a counting query might work.

Select recipe #, count(*) from relationship
where Ingredient # in ( 10, 14, 22 )
order by count(*) ;

That should get you on the right path.

Another comment befor you go too far on your system. Quantity would be
better as two attributes:
Quantity NUMBER,
UOM CHAR

where UOM stands for Unit of Measure.

You will need these split if you even need to convert the recipe (for
example to double it for a party or halve it for a two person dinner.)

HTH,
ed

Are you suggesting I add a column to my recipe table as 'Ingredient
Count'? If so, then that makes sense and I can make it work from
there...thank you!

Oh btw, I actually do have the quantity # and label as two different
columns, I just combined them here for simplicity. Thank you for
pointing that out though.

Ed Prochak wrote:

Hodge.Nate (AT) gmail (DOT) com wrote:

I use a Pizza-database for my trainings to ask similar questions :-)

create view PizzaView as
select
P.Pizza#
,P.PizzaName
,T.Topping
from
Pizzas P
join
PizzaToppings PT
on P.Pizza# = PT.Pizza#
join
Toppings Z on PT.Topping# = T.Topping#
;

Some of those questions are:

1. Return all pizzas which are a subset of the searched toppings.
*At least* toppings ('tomatoes', 'mozzarella', 'salami') and maybe
additional toppings:

select
Pizza#
,PizzaName
,count(*) as #Toppings
from
PizzaView
where
Topping in ('tomatoes', 'mozzarella', 'salami')
group by Pizza#, PizzaName
having count(*) = 3
;



2. Return all pizzas which are a superset of the searched toppings.
*At most* toppings ('tomatoes', 'mozzarella', 'salami'), but no other
toppings:

select
Pizza#
,PizzaName
,count(*) as #Toppings
from
PizzaView
group by Pizza#, PizzaName
having
sum(case when Topping in ('tomaten', 'mozzarella', 'salami') then 0
else 1
end) = 0
order by #Toppings desc
;



3. Return all pizzas which are a exactly made of the searched toppings.
*All toppings* ('tomatoes', 'mozzarella', 'salami'), but no other toppings:

select
Pizza#
,PizzaName
,count(*) as #Toppings
from
PizzaView
group by Pizza#, PizzaName
having
sum(case when Topping in ('tomaten', 'mozzarella', 'salami') then 1
else -1
end) = 3
;

Of course each question might be answered with lots of different
solutions, but those aggregates are easy to extend to any number of
toppings and usually fast and efficient.



If you got a table with available Toppings:
create table available_Toppings
(Topping varchar(30) not null primary key
);

/*** 1. ***/
select
p.Pizza#
,p.PizzaName
,count(*) as #Toppings
from
PizzaView p
join available_Toppings a on p.Topping = a.Topping
group by p.Pizza#, p.PizzaName
having
count(*) = (select count(*) from available_Toppings)
;


/*** 2. ***/
select
p.Pizza#
,p.PizzaName
,count(*) as #Toppings
from
PizzaView p
left join available_Toppings a on p.Topping = a.Topping
group by p.Pizza#, p.PizzaName
having
count(*) = count(a.Topping)
;


/*** 3. ***/
select
p.Pizza#
,p.PizzaName
,count(*) as #Toppings
from
PizzaView p
left join available_Toppings a on p.Topping = g.Topping
group by p.Pizza#, p.PizzaName
having
sum(case when a.Topping is not null then 1 else -1 end)
= (select count(*) from available_Toppings)
;


Dieter

Hodge.Nate (AT) gmail (DOT) com wrote:
NO! As Deiter pointed out, you do it in the query.
Read his post.

Ed
PS Top posting in newsgroups is considered rude by some.
It is at least inconvenient.

Dieter Noeth<dnoeth (AT) gmx (DOT) de> 08/28/06 1:30 PM >>>
I am suddenly very hungry.

Frank Swarbrick wrote:

That's why i do that lab usually before lunch, especially when i know
they serve pizza that day :-)

And i favour it over my previous version: a cocktail database...

Dieter

Dieter Noeth wrote:
yes. yes. yes. That is exactly what I was looking for! It took me
some time to figure it all out, I had no idea SQL was so powerful... I
had to modify it a little bit because I'm using Access (which doesn't
like 'CASE'), but totally amazing.

BTW, I actually am making a cocktail application for myself, that's
very funny.