Professional Web Applications Themes

three table outer join with aggregate functions - Coldfusion Database Access

here is a query joining three tables (SQL 2000) that doesn't return the results i need. SELECT o.OutID, o.GroupID, o.OutTitle, o.SubHead, o.OutOrder, COUNT(q.QuestionID) AS NumQuestions, COUNT(f.FlashCardID) AS NumCards FROM Outline o LEFT OUTER JOIN Questions q ON q.OutID = o.OutID LEFT OUTER JOIN Flashcards f ON f.OutID = o.OutID WHERE o.GroupID = #URL.GroupID# GROUP BY o.OutID, o.GroupID, o.OutTitle, o.SubHead, o.OutOrder ORDER BY o.OutOrder In theory this should give me the info from the Outline table and then count up the total number of questions and flashcards that are associated with each Outline item (if any) I don't get any errors, ...

  1. #1

    Default three table outer join with aggregate functions

    here is a query joining three tables (SQL 2000) that doesn't return the results
    i need.

    SELECT o.OutID, o.GroupID, o.OutTitle, o.SubHead, o.OutOrder,
    COUNT(q.QuestionID) AS NumQuestions,
    COUNT(f.FlashCardID) AS NumCards
    FROM Outline o
    LEFT OUTER JOIN Questions q ON q.OutID = o.OutID
    LEFT OUTER JOIN Flashcards f ON f.OutID = o.OutID
    WHERE o.GroupID = #URL.GroupID#
    GROUP BY o.OutID, o.GroupID, o.OutTitle, o.SubHead, o.OutOrder
    ORDER BY o.OutOrder

    In theory this should give me the info from the Outline table and then count
    up the total number of questions and flashcards that are associated with each
    Outline item (if any)

    I don't get any errors, but the results are not correct for the count's. If
    there are 2 questions and 3 flashcards for a given Outline item, both the
    NumQuestions and NumCards variable return a 6.

    works fine if I only work with one of the tables (Questions or Flashcards) but
    not when I try to combine both at once.

    I'm stumped - any ideas?

    jhilty Guest

  2. #2

    Default Re: three table outer join with aggregate functions

    so here's one solution - adding DISTINT to the count aggregate functions seems to work

    COUNT(DISTINCT(q.QuestionID)) AS NumQuestions,
    COUNT(DISTINCT(f.FlashCardID)) AS NumCards
    jhilty Guest

  3. #3

    Default Re: three table outer join with aggregate functions

    Thank you very much! I've been trying to solve this similar problem with no luck for the past 2 days!

    Now I can finally move on with my project balance sheet!
    Unregistered Guest

Similar Threads

  1. Outer join?
    By Ignoramus23298 in forum MySQL
    Replies: 5
    Last Post: May 22nd, 09:06 AM
  2. Left Outer Join
    By Jared in forum MySQL
    Replies: 0
    Last Post: May 3rd, 03:31 AM
  3. OUTER JOIN
    By jorgepino in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: May 23rd, 12:59 PM
  4. FULL OUTER JOIN
    By beckydub in forum Coldfusion Database Access
    Replies: 1
    Last Post: May 13th, 02:54 PM
  5. Outer Join Cursors and Nulls
    By PM \(pm3iinc-nospam\) in forum IBM DB2
    Replies: 0
    Last Post: August 1st, 06:56 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139