VBScript - Database - General - Finding and displaying duplicates

hi,

I'm trying to write a bit of SQL to find and display the duplicates in my DB.

Let's say I have a list of books that have been read by a number of people

so, I have a table called Books_read

Book_Name ------ Reader_name
book 1------------- Jim
book 2------------- Jim
book 3------------- Fred
book 4------------- Bob
book 1------------- bob

the query below (I was hoping) would give me

Book 1 -------- Jim
book 1 -------- bob

so thereby identifying the book that has been read by more than one person, and the names of the readers

strSQL = "select * from books_read where book_name in (select book_name from books_read group by book_name having count(distinct reader_name) >1) order by book_name"

but, this gives me a syntax error.

can anyone help?

thanks

--> Moved to SQL forum

I think this should work with extra brackets:

Perfect. Thank you very much. You wont believe how many different variations of code I've tried (even copying the data to a second table and doing compares!!)

You're welcome, glad it helped!!

I think I'm also trying to do stuff which is too complicated for me which is why I'm struggling.

in my explanation above I simplified the DB structure to show reader_name. Because I spent some time normalising my DB, the reader_name is actually a number which references a second table

so, I've actually got

Book_Name ------ Reader_name
book 1------------- 1
book 2------------- 1
book 3------------- 2
book 4------------- 3
book 1------------- 3

where the number 1, corresponds to the user table record which has Jim as the name. Therefore if Jim ever changes his name, all corresponding read books will reference correctly, (a tip for those who are just starting out with DB design - if Jim changed his name and I had my original DB structure, I would have to change his name in more than one place).

anyway the result of the query you supplied (very grateful) gives me

book 1 - 1
book 1 - 3

Ideally, I could join to the user_table to convert the '1' to 'Jim'

so..that gives me something like

strSQL = "SELECT book_name, reader_record FROM books_read " & _
"WHERE (book_record IN " & _
"(SELECT book_name FROM books_read GROUP BY book_name " & _
"HAVING (COUNT(reader_record) >1))) " & _
"ORDER BY book_name" & _
"INNER JOIN books_read ON USERS.record_id = books_read.reader_record "


which, I was hoping would give me

book 1 - Jim
book 1 - Bob

because the recod IDs 1 and 3 would be replaced with the user name from the second table. I'm guessing this is possible but I don't have a clue.

At the moment I'm using a second query after the first.

so I'm running the first query, and then running a second query based on the results of the first, and within the loop. Which works, but it's very infficient I'm guessing

i.e.
1st Query
select something from somewhere
while not rs.eof
something = rs("something")
2nd query based on the results of the first
select something_else from somewhere_else where something = x
loop back to first query

Hi, you should be able to adapt the query to join to your table of names - I have called it readers and I assume that it has two columms reader_id and reader_name:

What I wouldn't give to have your skills!!!

genius, thanks.

You're welcome, glad to help!!

Hi, I hope that the below query will be useful for you



Note: test2 is a table name

Thanks & Regards
Sakthimeenakshi.S