Display the all members of staff in the same department as Tony West : Join Table « Join « SQL / MySQL

1. SQL / MySQL
2. Join
3. Join Table

Display the all members of staff in the same department as Tony West

      
mysql>
mysql>
mysql> CREATE TABLE IF NOT EXISTS staff
    -> (
    ->   dept   CHAR(20)        NOT NULL,
    ->   name   CHAR(20)        PRIMARY KEY
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> # insert 6 records into the "staff" table
mysql> INSERT INTO staff (dept, name) VALUES ("Sales", "Tom");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO staff (dept, name) VALUES ("Legal", "Max");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO staff (dept, name) VALUES ("Works", "Ed");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO staff (dept, name) VALUES ("Sales", "Sue");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO staff (dept, name) VALUES ("Works", "Al");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO staff (dept, name) VALUES ("Sales", "Tony");
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> # display all data in the "staff" table
mysql> SELECT * FROM staff;
+-------+------+
| dept  | name |
+-------+------+
| Sales | Tom  |
| Legal | Max  |
| Works | Ed   |
| Sales | Sue  |
| Works | Al   |
| Sales | Tony |
+-------+------+
6 rows in set (0.00 sec)

mysql>
mysql> # display the all members of staff in the same department as Tony West
mysql> SELECT   s1.dept AS Department, s1.name AS Name
    -> FROM     staff AS s1, staff AS s2
    -> WHERE    s1.dept = s2.dept AND s2.name = "Tony West";
Empty set (0.00 sec)

mysql>
mysql> # delete this sample table
mysql> DROP TABLE IF EXISTS staff;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>

   
    
    
    
    
    
  

Related examples in the same category