比较并提取剩余记录列表Php Mysql

Hi All I am working on a friends system like facebook I have following tables:-

Table users
id
username
email
password
datecreated
etc....

Table friends
id
user1
user2
datemade

my questions are as follow:-

  1. How to fetch records (users list) not in friends table (user id not in friends table). for example :-select * from users not in friends table
  2. How to compare users id with other two fields in friends table user1 or user2

Question 1.

SELECT * FROM users WHERE id NOT IN(SELECT id FROM  friends)

Please provide the clear description of the table names and their corresponding column names and properties.

As per my understanding, below is probable solution.

1) select * from users u, friends f where f.user1="xyz" and f.user2<> { select user1, user2 from friends where user1 = "xyz" or user2="xyz"};

Note: I haven't tested the above query. If any error post it will try to rectify.

2) first of all there needs to be some connection between the two tables i.e. primary key and foreign key and also needs normalisation then the comparison can be made. From the above tables there is no common factor between the two tables, so comparison cannot be made. (I thinking that id in both table are different.)


Following are the answer for your queries :-

1. For the first question you have to use the following query. i.e

SELECT * FROM users WHERE id NOT IN (SELECT id FROM friends)


Hope it will solve your problems. Looking forward for comments if any
Thanks.