How can i write a query: select from table where a pair of record is not there?

[colap]

How can i write a query: select from table where a pair of record is not there?

mysql> describe users;+-------------+---------------------+------+-----+---------+----------------+| Field       | Type                | Null | Key | Default | Extra          |+-------------+---------------------+------+-----+---------+----------------+| id          | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment || username    | varchar(255)        | YES  |     | NULL    |                || password    | varchar(255)        | YES  |     | NULL    |                || email       | varchar(255)        | YES  |     | NULL    |                || email_token | varchar(255)        | NO   |     | NULL    |                || is_active   | tinyint(1)          | YES  |     | NULL    |                || created     | datetime            | YES  |     | NULL    |                || modified    | datetime            | YES  |     | NULL    |                |+-------------+---------------------+------+-----+---------+----------------+8 rows in set (0.00 sec)mysql> describe friends;+-----------+---------------------+------+-----+---------+----------------+| Field     | Type                | Null | Key | Default | Extra          |+-----------+---------------------+------+-----+---------+----------------+| id        | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment || user_id   | bigint(20) unsigned | YES  |     | NULL    |                || friend_id | bigint(20) unsigned | YES  |     | NULL    |                || created   | datetime            | YES  |     | NULL    |                || modified  | datetime            | YES  |     | NULL    |                |+-----------+---------------------+------+-----+---------+----------------+5 rows in set (0.01 sec)

How can i get all users where a user id not equal to login_user_id and this user_id and friend_id pair is missing in friends table? That means they are not friends.

 

Thanks in advance.

[Barand]

Find users not = logged user and match using left join against friends of logged user and see which are missing

SELECT u.id
, u.name as non_friend
FROM
	user u
LEFT JOIN 
	friend f ON u.id = f.friend_id AND f.user_id = $loggedUser
WHERE u.id <> $loggedUser 
	AND f.friend_id IS NULL
ORDER BY id

[colap]

 

Find users not = logged user and match using left join against friends of logged user and see which are missing

SELECT u.id
, u.name as non_friend
FROM
	user u
LEFT JOIN 
	friend f ON u.id = f.friend_id AND f.user_id = $loggedUser
WHERE u.id <> $loggedUser 
	AND f.friend_id IS NULL
ORDER BY id

Can't understand the query. Can you please explain more(elaborate) with example ?

[Barand]

the data

mysql> SELECT id, name FROM user;
+----+--------+
| id | name   |
+----+--------+
|  1 | user1  |
|  2 | user2  |
|  3 | user3  |
|  4 | user 4 |
|  5 | user 5 |
+----+--------+

mysql> SELECT id, user_id, friend_id FROM friend;
+----+---------+-----------+
| id | user_id | friend_id |
+----+---------+-----------+
|  1 |       1 |         2 |
|  2 |       1 |         3 |
|  3 |       2 |         3 |
|  4 |       2 |         4 |
|  5 |       4 |         5 |
+----+---------+-----------+

Suppose I am logged in as user #2.

 

The main part of the query finds all my possible friends (IE all users that are not me). So leaving out the LEFT JOIN we have

SELECT u.id
, u.name
FROM
    user u
WHERE u.id <> 2 
ORDER BY id;

+----+--------+
| id | name   |
+----+--------+
|  1 | user1  |
|  3 | user3  |
|  4 | user 4 |
|  5 | user 5 |
+----+--------+

So having got the possible friends we want to compare that list agianst the friends I already have to see which ones are missing.

SELECT u.id                          SELECT user_id
, u.name                             , friend_id
FROM                                 FROM 
    user u                               friend
WHERE u.id <> 2                      WHERE user_id = 2
ORDER BY id

+----+--------+                      +---------+-----------+
| id | name   |                      | user_id | friend_id |
+----+--------+                      +---------+-----------+
|  1 | user1  |                      |       2 |         3 |
|  3 | user3  |                      |       2 |         4 |
|  4 | user 4 |                      +---------+-----------+
|  5 | user 5 |                                      | 
+----+--------+                                      |
   |                                                 |
   |                                                 |
   |                                                 |
   +-------------------LEFT JOIN---------------------+

The way to do this is to use a LEFT JOIN which will get the data from users and friends. Where there is no matching friend record the fields from friend are null.

To do this we use

FROM
    user u
LEFT JOIN 
	friend f ON u.id = f.friend_id AND f.user_id = 2

which takes those friend records for user #2 and matches the possible user ids against the existing friend ids

 

The query is now

SELECT u.id
, u.name 
, f.friend_id
FROM
    user u
LEFT JOIN 
    friend f ON u.id = f.friend_id AND f.user_id = 2
WHERE u.id <> 2 
ORDER BY id;
+----+--------+-----------+
| id | name   | friend_id |
+----+--------+-----------+
|  1 | user1  |      NULL |
|  3 | user3  |         3 |
|  4 | user 4 |         4 |
|  5 | user 5 |      NULL |
+----+--------+-----------+

Nearly there!

We label the name column with an alias "non_friend" (as that is what they are). We only wanted the friend_id column to show the nulls, so that can go. Lastly, we are only interested in those with NULL values (non-matching) so we add an extra condition to the the WHERE clause

AND f.friend_id IS NULL

which gives the final version of the query

SELECT u.id
  , u.name as non_friend
FROM
    user u
LEFT JOIN 
    friend f ON u.id = f.friend_id AND f.user_id = 2
WHERE u.id <> 2 
     AND f.friend_id IS NULL
ORDER BY id;

+----+------------+
| id | non_friend |
+----+------------+
|  1 | user1      |
|  5 | user 5     |
+----+------------+

I hope that helps.

[colap]

In friend table,

 

select user_id,friend_id from friends where user_id=2;

Here, (2,3) and (2,4) . But (3,2) is same as (2,3) and (4,2) is same as (2,4) . Both are friends.

 

How do/did you handle this?

[Barand]

If you want to apply that rule then create a subquery to get both sets (user/friend and friend/user)

SELECT user_id
		  , friend_id
		FROM friend
		WHERE user_id = 2
			UNION
		SELECT friend_id
		  , user_id
		FROM friend
		WHERE friend_id = 2
+---------+-----------+
| user_id | friend_id |
+---------+-----------+
|       2 |         3 |
|       2 |         4 |
|       2 |         1 |
+---------+-----------+

then left join with that instead of the friend table

SELECT u.id
  , u.name as non_friend
FROM
    user u
LEFT JOIN 
    (
        SELECT user_id
          , friend_id
        FROM friend
        WHERE user_id = 2
            UNION
        SELECT friend_id
          , user_id
        FROM friend
        WHERE friend_id = 2
    ) f  ON u.id = f.friend_id
WHERE u.id <> 2 
     AND f.friend_id IS NULL
ORDER BY id;
+----+------------+
| id | non_friend |
+----+------------+
|  5 | user 5     |
+----+------------+

[colap]

How can i stop inserting same data into friends table during friend request? For example: (2,3) and (3,2) are same. If (2,3) is there in friends table, (3,2) will not be inserted. How can i check it to keep data integrity.

[Barand]

You could set a rule that the lower id is the user and higher id is the friend when adding the record and put a unique constraint on (user_id, friend_id)

[colap]

http://www.w3schools.com/sql/sql_unique.asp

CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName) 

CONSTRAINT uc_PersonID UNIQUE (LastName,P_Id)

Are these same?

[Barand]

yes, but neither will prevent you adding (2,3) and (3,2)

[colap]

If there is (2,3) then it will not insert (3,2). Do i have to check this programmatically?

[Barand]

I did suggest how you might do it.

[colap]

SELECT * FROM users WHERE NOT EXISTS ( SELECT * FROM friends WHERE (user_id = users.id AND friend_id = 2) OR (user_id = 2 AND friend_id = users.id) )

This outputs:

(2, 'user2', NULL, NULL),
(5, 'user5', NULL, NULL)

SELECT u.id
, u.name as non_friend
FROM
user u
LEFT JOIN 
(
SELECT user_id
, friend_id
FROM friend
WHERE user_id = 2
UNION
SELECT friend_id
, user_id
FROM friend
WHERE friend_id = 2
) f ON u.id = f.friend_id
WHERE u.id <> 2
AND f.friend_id IS NULL
ORDER BY id;
+----+------------+
| id | non_friend |
+----+------------+
| 5 | user 5      |
+----+------------+

Is it possible to do the same thing with NOT EXISTS ?

 

Thanks in advance.

[Barand]

Yes, but beware of dependent subqueries where you perform a separate query for every record to see if a match exists. This kills query performance. Joins don't have that problem

[colap]

Yes, but beware of dependent subqueries where you perform a separate query for every record to see if a match exists. This kills query performance. Joins don't have that problem

What will be the sql query with NOT EXISTS to get the same result? Can you paste the sql query here?

[Barand]

You need to exclude yourself (user #2) from the users

SELECT * FROM user 
WHERE user.id <> 2
AND NOT EXISTS 
   ( 
    SELECT * 
    FROM friend 
    WHERE (user_id = user.id AND friend_id = 2) 
	OR (user_id = 2 AND friend_id = user.id) 
   );

If you do an "EXPLAIN" of a query and see a DEPENDENT SUBQUERY use a join instead

mysql> EXPLAIN SELECT * FROM user
    -> WHERE NOT EXISTS (
    ->     SELECT *
    ->     FROM friend
    ->     WHERE (user_id = user.id AND friend_id = 2)
    ->         OR (user_id = 2 AND friend_id = user.id)
    ->     )
    -> AND user.id <> 2;
+----+--------------------+--------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type        | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+--------------------+--------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | PRIMARY            | user   | range | PRIMARY       | PRIMARY | 4       | NULL |    3 | Using where              |
|  2 | DEPENDENT SUBQUERY | friend | index | idx2          | idx2    | 10      | NULL |    5 | Using where; Using index |
+----+--------------------+--------+-------+---------------+---------+---------+------+------+--------------------------+

[colap]

Yes, but beware of dependent subqueries where you perform a separate query for every record to see if a match exists. This kills query performance. Joins don't have that problem

 

http://stackoverflow.com/questions/915643/select-where-not-exists

 

Here it says: "You can join these tables with a LEFT JOIN keyword and filter out the NULL's, but this will likely be less efficient than using NOT EXISTS."

 

NOT EXISTS is more efficient ?

[Barand]

two medium-sized tables

mysql> SELECT COUNT(*) FROM tablea;
+----------+
| COUNT(*) |
+----------+
|    11956 |
+----------+

mysql> SELECT COUNT(*) FROM tableb;
+----------+
| COUNT(*) |
+----------+
|   654071 |
+----------+

Look for missing dates

mysql> SELECT COUNT(DISTINCT thedate) as dates
    -> FROM tablea A
    -> LEFT JOIN tableb B
    ->     ON A.thedate = B.DATA_COMPETENZA
    -> WHERE b.DATA_COMPETENZA IS NULL;
+-------+
| dates |
+-------+
| 10525 |
+-------+
1 row in set (0.05 sec)

mysql> SELECT COUNT(DISTINCT thedate) as dates
    -> FROM tablea A
    -> WHERE NOT EXISTS
    ->     (
    ->     SELECT * FROM tableb
    ->     WHERE DATA_COMPETENZA = A.thedate
    ->     );
+-------+
| dates |
+-------+
| 10525 |
+-------+
1 row in set (0.08 sec)