CODEWITHSUNDEEP
sqlsql-serverduplicates
Manda Kirk
Manda Kirk

Reputation: 13

How to keep phone numbers out of the wrong fields. SQL

SQL Server 2014. I need to write a query which produces a listing of accounts that have EITHER a phone number or an email address.

Phone numbers and emails are kept in 2 different tables. I'm using an inner join.

My issue is, when I run my query, phone numbers are inserted into columns they don't belong.

It displays like

Record 1     [email protected]              555-555-5555
Record 2     [email protected]     555-555-5555

Record 2 should not have a phone number associated. It was just an email address. But for some reason, record 1's phone number is duplicating over.

My query is as follows:

SELECT DISTINCT 
    a.record_id, a.account_email, b.phone_number
FROM
    table1 AS a 
INNER JOIN
    Table2 AS b ON a.record_id = b.record_id 
                  OR a.record_id <> b.record_id 
                  AND a.account_email NOT IN ('NULL', '')
WHERE 
    (phone_number NOT IN (' ')

Upvotes: 0

Views: 61

Answers (2)

LazGradner
LazGradner

Reputation: 43

a.record_id = b.record_id OR a.record_id <> b.record_id is contradicting itself in your join. In example below, a/b being whatever table phone_number is located in.

Try this:

SELECT a.record_id, a.account_email, b.phone_number
FROM table1 AS a 
INNER JOIN table2 as b ON
a.record_id = b.record_id
WHERE a/b.phone_number IS NOT NULL
AND a.account_email IS NOT NULL

Upvotes: 0

Jonathan Hamel
Jonathan Hamel

Reputation: 1393

This is due because on your inner join clause you use a.record_id <> b.record_id

You should use a left join which will return nulls in the columns where there is no match.

Select Distinct a.record_id, a.account_email, b.phone_number
From table1 as a
LEFT JOIN Table2 as b ON a.record_id = b.record_id

Upvotes: 1

Related Questions