Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a user relation to address (1 to 1).

So the address has two types which is a type column.

One is the VerifiedAddress and the other one is the SearchAddress.

So the user who had verified his address has two entries in the addresses table.

In case you wonder why is that, the reason is that user can go to a travel and change the SearchAddress but the VerifiedAddress remains the same.

I'm trying to find all the users who haven't got verified their address. I have some user list from which I know who has verified address and who hasn't got it.

This is what I have tried the last, not exactly what I wanted :

select id from addresses
where user_id in (8112, 18620, 23, 19668, 10212)
and type = 'VerifiedAddress';

Gives me all the verified users, but if I change the type != VerifiedAddress I still get the same results because users who have verified their address have two entries in a addresses table, this time I get the user_ids from the SearchAddress.

How can I get this data from addresses table?

Note: SearchAddress can be null as well as VerifiedAddress. Naturally people who don't have searchaddress don't have the verified as well.

share|improve this question

1 Answer 1

up vote 4 down vote accepted

This is called a left anti-join. You want all users who don't have a verified address. So you do a left join to find all users and whether they do have a verified address, then discard all the ones that have a verified address.

Something like:

SELECT *
FROM users
LEFT OUTER JOIN addresses a
ON (users.id = a.user_id AND a.type = 'VerifiedAddress')
WHERE a.user_id IS NULL;

This may be easier to understand when expressed with NOT EXISTS as:

SELECT *
FROM users
WHERE NOT EXISTS (
  SELECT 1 
  FROM addresses a
  WHERE users.id = a.user_id
  AND a.type = 'VerifiedAddress'
)

both of which will optimize to the same query plan, in general.

share|improve this answer
    
thanks Craig, this was what I was looking for, does the using of aliases like addresses a, have any impact on the performance? –  Gandalf StormCrow Feb 20 at 18:13
    
@GandalfStormCrow: No measurable influence. –  Erwin Brandstetter Feb 21 at 0:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.