CODEWITHSUNDEEP
mysqlsql
AliBZ
AliBZ

Reputation: 4099

Query between two related tables

I have to tables. One of them, user_profile, has a field named user_profile_id and the other table, user_friend, has two fields named user_profile1_id and user_profile2_id (which are FK to the first table).

I want to check if there is any users in user_profile which is not in user_friend table, neither in user_profile1_id, nor user_profile2_id. what query should I use !?

Upvotes: 0

Views: 835

Answers (4)

amirmonshi
amirmonshi

Reputation: 659

First of all I suggest you to use a circular foreign key in the user_profile_table where the foreign key refers back to the primary key of the same table. This way you save yourself creating another table just for relationships between users.

However, using your design, you can use the following query:

SELECT user_profile_id 
FROM user_profile A
 WHERE 
  NOT EXISTS(
      SELECT * FROM user_friends B 
       WHERE B.user_profile1_id=A.user_profile_id OR 
             B.user_profile2_id=A.user_profile_id
            )

Upvotes: 2

Michael Berkowski
Michael Berkowski

Reputation: 270607

Try:

SELECT user_profile_id 
FROM user_profile
WHERE 
  user_profile_id NOT IN (SELECT DISTINCT user_profile1_id FROM user_friend)
  AND user_profile_id NOT IN (SELECT DISTINCT user_profile2_id FROM user_friend)

There are probably more efficient queries, but this should do the job.

Upvotes: 0

Jon Egerton
Jon Egerton

Reputation: 41539

Try (this is MSSQL but it should be similar in MySQL)

select 
   *
from
   user_profile up
   left join user_friend uf
     on up.user_profile_id = uf.user_profile1_id
     or up.user_profile_id = uf.user_profile2_id
where
     coalesce(uf.user_profile1_id,uf.user_profile2_id) is null

Upvotes: 1

azat
azat

Reputation: 3565

Try this


SELECT * FROM users
LEFT JOIN friends f1 ON (users.id = friends.uid)
LEFT JOIN friends f2 ON (users.id = friends.fid)
WHERE f1.uid IS NULL AND f2.uid IS NULL

Upvotes: 1

Related Questions