results giving duplicates

db2Noob · #1 (**permalink**) 08-17-10, 19:51

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

n_i · #2 (**permalink**) 08-17-10, 22:09

Quote:

Originally Posted by db2Noob

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.

db2Noob · #3 (**permalink**) 08-18-10, 09:03

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,

dav1mo · #4 (**permalink**) 08-18-10, 09:18

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

n_i · #5 (**permalink**) 08-18-10, 10:00

Quote:

Originally Posted by db2Noob

With or without distinct in the count is the same count.

Would you mind showing both actual queries and the actual results?

tonkuma · #6 (**permalink**) 08-18-10, 10:16

Quote:

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech