var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: distinct on normalized data?
The title probably isn't very descriptive. Lets say I have two tables:
So we might have:
1 | Deserts | Active
2 | Entree | Active
and then we'd have something like
which might have
234| Pasta | 2 | John Doe
5123| Apple Pie |1 | Granny Doe
What I want to get is a list of Distinct() Categories, iif they have active children in the Recipes table.
Ok, knowing that, let's pretend there are 12 million records in the Recipes table, and there are 15 or so in the category table. What I would normally do is something like:
SELECT Distinct(Name) FROM Recipes where status='A'
That will eventually return the correct answer, but I can't help but think there is a more efficient way to achieve that.
Some type of join? Will that actually return the data faster?
I think you're asking for
This should be quicker than scanning all of the recipes table, since each Active category only needs to find one match. Should go even faster if r.category is indexed.
select id, name
from category c
where status = 'Active' and
exists (select 1
from recipes r
where c.id = r.category);
that seemed to do it
Thanks! that seemed to do it. Query time dropped by about a factor of ~7.5.
EDIT: Make that an increase of a factor of 140 or so (I left some things in my query I shouldnt have)
I thought there must be an easier way.
Last edited by jholder; 06-11-07 at 18:33.