Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2010
    Posts
    9

    Unanswered: results giving duplicates

    Hi all,

    I have a problem when counting the total results when I'm gathering information from 3 tables.

    I want to return all the values in the Title column in Table1 that are wanted (Wanted column = yes) and the total number of documents (docId) from each of those from Table3.
    Because there's no FolderId in Table3, I need to use Table2 that has FolderId and index to link the two.
    Note: many index numbers can belong to a single docId

    Here's an example:

    Code:
    Table1
    FolderId         Title       Wanted
    folder1           title1      yes
    folder2           title2      yes
    folder3           title3      no
    folder4           title4      yes
    Code:
    Table2
    FolderId         index
    folder1           index1
    folder2           index2
    folder3           index3
    folder4           index4
    folder2           index5
    folder1           index6
    folder2           index7
    Code:
    Table3
    docId          index
    doc1           index1
    doc2           index2
    doc3           index3
    doc4           index4
    doc1           index6
    doc2           index5
    doc3           index3
    doc5           index7
    The result table should then be:

    Code:
    Query Result
    Title       docId
    title1      1
    title2      2
    title4      1
    The query I'm trying is:

    Code:
    SELECT Table1.Title, COUNT(Table3.docId)
    FROM Table1 LEFT OUTER JOIN Table2 ON Table1.FolderId=Table2.FolderId
    LEFT OUTER JOIN Table3 ON Table2.index=Table3.index
    WHERE Table1.Wanted='yes'
    GROUP BY Table1.Title
    ORDER BY Table1.Title DESC
    It does return the Titles I want, it's only that the count is giving more than it should.

    I'm not sure if i'm clear enough so please let me know what i need to specify better.

    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by db2Noob View Post

    Code:
    SELECT Table1.Title, COUNT(DISTINCT Table3.docId)
    FROM Table1 
    ...
    By the way, OUTER joins are unnecessary in your example, you should use INNER joins.

  3. #3
    Join Date
    Aug 2010
    Posts
    9
    Hi n_i, thanks for your response.
    I changed the inner joins and added the distinct but I'm getting the same wrong count. With or without distinct in the count is the same count.

    any other ideas?

    Thanks,

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Hey Noob,
    Remove table2 from your query. It does nothing for you, it is not limiting the amount of data being returned, it is not used to join table1 to table3. So, the only thing it does do is throw off your counts, as there are multiple indexes there.
    Dave

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by db2Noob View Post
    With or without distinct in the count is the same count.
    Would you mind showing both actual queries and the actual results?

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I changed the inner joins and added the distinct but I'm getting the same wrong count. With or without distinct in the count is the same count.
    I got the result what you wanted, like this:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     Table1(FolderId , Title , Wanted) AS (
    VALUES
      ('folder1' , 'title1' , 'yes')
    , ('folder2' , 'title2' , 'yes')
    , ('folder3' , 'title3' , 'no' )
    , ('folder4' , 'title4' , 'yes')
    )
    ,Table2(FolderId , index) AS (
    VALUES
      ('folder1' , 'index1')
    , ('folder2' , 'index2')
    , ('folder3' , 'index3')
    , ('folder4' , 'index4')
    , ('folder2' , 'index5')
    , ('folder1' , 'index6')
    , ('folder2' , 'index7')
    )
    ,Table3(docId , index) AS (
    VALUES
      ('doc1' , 'index1')
    , ('doc2' , 'index2')
    , ('doc3' , 'index3')
    , ('doc4' , 'index4')
    , ('doc1' , 'index6')
    , ('doc2' , 'index5')
    , ('doc3' , 'index3')
    , ('doc5' , 'index7')
    )
    SELECT Table1.Title , COUNT(DISTINCT Table3.docId) AS docId
     FROM  Table1
     INNER JOIN
           Table2
       ON  Table1.FolderId = Table2.FolderId
     INNER JOIN
           Table3
       ON  Table2.index = Table3.index
     WHERE Table1.Wanted = 'yes'
     GROUP BY
           Table1.Title
     ORDER BY
           Table1.Title ASC
    ;
    ------------------------------------------------------------------------------
    
    TITLE  DOCID      
    ------ -----------
    title1           1
    title2           2
    title4           1
    
      3 record(s) selected.

Posting Permissions

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