CODEWITHSUNDEEP
sqlsql-server
JimmyPop13
JimmyPop13

Reputation: 317

Finding data that isn't there

Lets say I run a sql query on a user to return contact types. User A returns three rows:

555-1234

555-4321

[email protected]

Now I want to find users who have no phone number but may have an email address. Can anyone give me advice on how I would begin to go about that? If more information is needed please ask.

Edit: Sorry currently on the move. Will post useful data later. For now I can say that there are not strict columns. Eg, Phone1, Phone2, Email.

It's more open, as in the column currently takes a string so it can have a phone number or email. So some users have several entries in the table whereas some may have just one.

I am trying to work out how to find users who have no phone number entries. Hope this makes a little more sense now.

Upvotes: 0

Views: 192

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270361

If you just want users with no phone number:

select u.*
from users u
where not exists (select 1
                  from contact c
                  where u.userId = c.userId and
                        c.contactType = 'Phone'
                 );

You don't specify that you actually want the email address (if any), so this should be the most efficient way to get the users.

Upvotes: 0

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

Lets say,you have tables named User and Contact and for a user there are multiple contacts in the contact table.

Use the below query to find users who have no phone number but may have an email address.

SELECT *
FROM User u
 JOIN Contact c ON u.userId = c.userId and c.contactType='Email'
WHERE NOT EXISTS (SELECT 1 FROM Contact c1 WHERE u.userId = c1.userID and c.contactType='Phone')

Upvotes: 2

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

use null check and email column length check as you said may email thats why i checked with null or some value 

select * from users where phone_number is null and (email is null or LEN(trim(email))>0)

Upvotes: 0

gbn
gbn

Reputation: 432421

Assuming you mean simply "No Phone" because there is no such comparison operator "may have"

Single table:

SELECT * FROM Usertable WHERE Phone1 IS NULL AND Phone2 IS NULL

User+Contact tables:

SELECT * 
FROM Users U
WHERE NOT EXISTS (
          SELECT * 
          FROM Contacts C 
          WHERE U.UserID = C.UserID AND ContactType = 'Phone'
          )

Upvotes: 1

Related Questions