Jump to content

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


colap

Recommended Posts

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.

Edited by php-coder
Link to comment
Share on other sites

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
Link to comment
Share on other sites

 

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 ?

Edited by php-coder
Link to comment
Share on other sites

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.

Edited by Barand
  • Like 2
Link to comment
Share on other sites

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     |
+----+------------+
Edited by Barand
Link to comment
Share on other sites

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.

Edited by php-coder
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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 |
+----+--------------------+--------+-------+---------------+---------+---------+------+------+--------------------------+
Link to comment
Share on other sites

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 ?

Edited by php-coder
Link to comment
Share on other sites

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)

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.