Mysql – Query between two related tables


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?


CREATE TABLE user_profile (

CREATE TABLE user_friend (
  user_profile1_id INT,
  user_profile2_id INT

Best Answer

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