Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2006

    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?



  2. #2
    Join Date
    Dec 2003
    I think you're asking for
    select id, name
    from category c
    where status = 'Active' and
          exists (select 1
                  from  recipes r
                  where = r.category);
    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.


  3. #3
    Join Date
    Mar 2006

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts