SQL inner join question
Posted on 2007-10-11
I have two tables, DB_ALL_1 and DB_ALL_2, the first has 1,200 records, the second has 13,000 records. What I want to do is run a query that lists those phone numbers that are common to both tables.
So, I wrote an SQL Inner Join query, but for some reason, it's returning 7,300 records. Intuitively, the number of records that are common to both tables has to be less than the number of records in the smaller table (1,200), yes. However, there are many duplicate phone numbers in the result.
So, I would most appreciate a correction to the code so that only the unique phone numbers are extracted. I tried a 'DISTINCT' phrase after 'SELECT', but the result still returned 7,300.
SQLStr = "SELECT DISTINCT * FROM DB_ALL_1" & _
" INNER JOIN DB_ALL_2" & _
" ON DB_ALL_1.ACC_PHONE = DB_ALL_2.ACC_PHONE" & _
" WHERE DB_ALL_1.ACC_PHONE = DB_ALL_2.ACC_PHONE"
Recordset1.Open SQLStr, DbsConnection, adOpenStatic
RecordCount = Recordset1.RecordCount
While Not Recordset1.EOF
Phone = Recordset1("DB_ALL_1.ACC_PHONE")