We help IT Professionals succeed at work.

SQL inner join question

BrianBeck
BrianBeck asked
on
315 Views
Last Modified: 2010-03-20
Dear Gurus
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
             Recordset1.MoveLast
             Recordset1.MoveFirst
             RecordCount = Recordset1.RecordCount

             While Not Recordset1.EOF
                         Phone = Recordset1("DB_ALL_1.ACC_PHONE")
                         Recordset1.MoveNext
             Wend
Comment
Watch Question

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
Hello cclambie,

if you put Distinct *, it will consider all the columns and will take the distict among those rows for eg, if the values in the able are

a , b , 2007-01-01 01:01:001
a , b , 2007-01-01 01:01:002
a , b , 2007-01-01 01:01:003

Now the distinct statement will return all the rows, as they differ data on the last column




Aneesh R
Your join and where clause are redundant and both can be replaced with a subquery:
INNER JOIN (SELECT DISTINCT ACC_PHONE FROM DB_ALL_2) A2
ON A2.ACC_PHONE = DB_ALL_1.ACC_PHONE

Your intuition is off in this case, it would appear that there is a one-to-many (or many to many) relationship between the tables.  Your distinct call ensures that the each record of the result is unique but by including all the columns you get more results than 1200.  

Author

Commented:
Thank you all.  I've increased the points to 500 and split between the solution providers, angelIII: and asvforce.
Thanks!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.