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 u开发者_运维知识库ser_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 !?
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
)
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
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
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.
精彩评论