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 two tables. One of them (user_profile table) has a field named "user_profile_id" and the other one (user_friend table) has two fields named "user_profile1_id" and "user_profile2_id" which are FK to the first table.

I want to check if there are any users in user_profile which are not in user_friend table, neither in user_profile1_id, nor user_profile2_id.

What query should I use?

EDIT:

CREATE TABLE user_profile (
  --fields
)

CREATE TABLE user_friend (
  user_profile1_id INT,
  user_profile2_id INT
)
share|improve this question

1 Answer 1

up vote 3 down vote accepted

Use a LEFT JOIN and check for null values. Something like:

SELECT profile.* FROM user_profile profile
 LEFT JOIN user_friend friend1 ON friend1.user_profile1_id = user_profile_id
 LEFT JOIN user_friend friend2 ON friend2.user_profile2_id = user_profile_id
 WHERE friend1.user_profile1_id IS NULL AND friend2.user_profile2_id IS NULL

Here's a link to the various joins

share|improve this answer
    
Is there any way without join? because user_friend table has 20 million records! –  AliBZ Feb 24 '11 at 15:03
2  
@alibz I think you seriously misunderstand databases. They are designed for working with sets, and JOIN is the most basic set op. –  jcolebrand Feb 24 '11 at 15:06
    
thanx DTest, it worked great ;) –  AliBZ Feb 24 '11 at 15:52

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.