Finding people who didn't send mail to themselves

       
mysql>
mysql> CREATE TABLE mail
    -> (
    ->  t               DATETIME,       # when message was sent
    ->  senderUser      CHAR(8),        # sender (source user and host)
    ->  senderHost      CHAR(20),
    ->  recipientUser   CHAR(8),        # recipient (destination user and host)
    ->  recipientHost   CHAR(20),
    ->  size    BIGINT,         # message size in bytes
    ->  INDEX   (t)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO mail (t,senderHost,senderUser,recipientHost,recipientUser,size)
    ->  VALUES
    ->          ('2010-05-11 10:15:08','saturn','barb','mars','tricia',58274),
    ->          ('2010-05-12 12:48:13','mars','tricia','venus','gene',194925),
    ->          ('2010-05-12 15:02:49','mars','phil','saturn','phil',1048),
    ->          ('2010-05-13 13:59:18','saturn','barb','venus','tricia',271),
    ->          ('2010-05-14 09:31:37','venus','gene','mars','barb',2291),
    ->          ('2010-05-14 11:52:17','mars','phil','saturn','tricia',5781),
    ->          ('2010-05-14 14:42:21','venus','barb','venus','barb',98151),
    ->          ('2010-05-14 17:03:01','saturn','tricia','venus','phil',2394482),
    ->          ('2010-05-15 07:17:48','mars','gene','saturn','gene',3824),
    ->          ('2010-05-15 08:50:57','venus','phil','venus','phil',978),
    ->          ('2010-05-15 10:25:52','mars','gene','saturn','tricia',998532),
    ->          ('2010-05-15 17:35:31','saturn','gene','mars','gene',3856),
    ->          ('2010-05-16 09:00:28','venus','gene','mars','barb',613),
    ->          ('2010-05-16 23:04:19','venus','phil','venus','barb',10294),
    ->          ('2010-05-17 12:49:23','mars','phil','saturn','tricia',873),
    ->          ('2010-05-19 22:21:51','saturn','gene','venus','gene',23992);
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT DISTINCT m1.senderUser
    -> FROM mail AS m1 LEFT JOIN mail AS m2
    -> ON m1.senderUser = m2.senderUser AND m2.senderUser = m2.recipientUser
    -> WHERE m2.recipientUser IS NULL;
+------------+
| senderUser |
+------------+
| tricia     |
+------------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql> drop table mail;
Query OK, 0 rows affected (0.00 sec)

Related examples in the same category

1.	Subqueries As Calculated Columns: Simple Subqueries
2.	Subqueries in the WHERE Clause 2
3.	Subqueries That Return Multiple Results
4.	Sub queries
5.	Sub query with string concatenate
6.	Sub query with not equal and order
7.	Sub query with calculation
8.	Update command with sub query
9.	Delete command with sub query
10.	SubSELECTs Syntax Variants
11.	Uses a subquery to return an AuthID value
12.	Use a not equal (<>) comparison operator in the WHERE clause to introduce the subquery
13.	Subquery uses an aggregate function to arrive at a value that the outer statement can use
14.	Nested subquery
15.	Four-level nested subquery with alias
16.	Nested subquery and where clause
17.	Minus value from subquery
18.	Subquery and equals operator(ERROR 1242 (21000): Subquery returns more than 1 row)
19.	Row values and subquery
20.	Nested three level subquery
21.	Greater than the result of subquery
22.	Equals to the result of subquery
23.	Row values comparison for subquery
24.	Select from the result of subquery
25.	Value calculation of subquery
26.	Subquery as a table
27.	Alias subquery
28.	Calculation in subquery
29.	Using function with result from subquery
30.	Create constant value in subquery
31.	Calculation in subquery and use the result from outside
32.	Equals operator with subquery
33.	Less than and subquery(ERROR 1242 (21000): Subquery returns more than 1 row)
34.	Less than data type value from subquery
35.	<=> and subquery
36.	Less than two values in subquery
37.	Greater than two values in subquery
38.	Get substring for a value from subquery
39.	Mix constant value and subquery
40.	Using the value from subquery with in operator
41.	Pair value within in operator and subquery
42.	In a list of value from subquery
43.	In operator and subquery
44.	Nested subquery and in operator
45.	Not in and subquery
46.	Between...and opertor and subquery
47.	Count value in subquery
48.	Add up count value from subquery
49.	From a subquery
50.	Aggregate function in subquery
51.	Having clause with subquery
52.	Count and subquery in Having clause
53.	Subquery with having clause
54.	Order by value from subquery
55.	Subquery with limit clause
56.	Insert statement with subquery
57.	Use a derived table.
58.	Searching for Titles Without Authors
59.	Compare to aggregate function
60.	Data calculation inside in operator
61.	COMPARISON OPERATORS WITH SUBQUERIES
62.	Select from three subqueries
63.	Select from two subqueries
64.	Row value comparison with select statement
65.	Compare two data type value together
66.	Nested subqueries
67.	Two USING clauses, rather than ON clauses

Finding people who didn't send mail to themselves : Sub query « Select Clause « SQL / MySQL

Related examples in the same category