Using temp table during table join

     
mysql>
mysql> CREATE TABLE author
    -> (
    ->     a_id INT UNSIGNED NOT NULL AUTO_INCREMENT, # author ID
    ->     name VARCHAR(30) NOT NULL, # author name
    ->     PRIMARY KEY (a_id),
    ->     UNIQUE (name)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE book
    -> (
    ->     a_id INT UNSIGNED NOT NULL, # author ID
    ->     p_id INT UNSIGNED NOT NULL AUTO_INCREMENT, # book ID
    ->     title VARCHAR(100) NOT NULL, # title of book
    ->     state VARCHAR(2) NOT NULL, # state where purchased
    ->     price INT UNSIGNED, # purchase price (dollars)
    ->     INDEX (a_id),
    ->     PRIMARY KEY (p_id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO author (name) VALUES
    ->  ('Tom'),
    ->  ('Monet'),
    ->  ('Jack'),
    ->  ('Picasso'),
    ->  ('Mary')
    -> ;
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql>
mysql> INSERT INTO book (a_id,title,state,price)
    ->  SELECT a_id, 'Database', 'IN', 34 FROM author WHERE name = 'Tom';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>
mysql> INSERT INTO book (a_id,title,state,price)
    ->  SELECT a_id, 'SQL', 'MI', 87 FROM author WHERE name = 'Tom';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>
mysql> INSERT INTO book (a_id,title,state,price)
    ->  SELECT a_id, 'MySQL', 'KY', 48 FROM author WHERE name = 'Jack';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO book (a_id,title,state,price)
    ->  SELECT a_id, 'XML', 'KY', 67    FROM author WHERE name = 'Jack';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO book (a_id,title,state,price)
    ->  SELECT a_id, 'Java', 'IA', 33   FROM author WHERE name = 'Jack';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>
mysql> INSERT INTO book (a_id,title,state,price)
    ->  SELECT a_id, 'HTML', 'NE', 64   FROM author WHERE name = 'Mary';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>
mysql> CREATE TABLE tmp
    -> SELECT a_id, MAX(price) AS max_price FROM book GROUP BY a_id;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT author.name, book.title, book.price
    -> FROM author, book, tmp
    -> WHERE book.a_id = tmp.a_id
    -> AND book.price = tmp.max_price
    -> AND book.a_id = author.a_id;
+------+-------+-------+
| name | title | price |
+------+-------+-------+
| Tom  | SQL   |    87 |
| Jack | XML   |    67 |
| Mary | HTML  |    64 |
+------+-------+-------+
3 rows in set (0.00 sec)

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

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

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

mysql>

Related examples in the same category

1.	Using a Join to Fill in Holes in a List
2.	Produce the same results by using a USING clause to qualify the join
3.	Specify the necessary join conditions in an ON or USING clause.
4.	Natural Joins and the USING Keyword
5.	Create the same report using the JOIN function in the following command.
6.	Using function in where clause during table joining
7.	Using table alias in table join
8.	Using key word USING, in which the common linking field is specified.

Using temp table during table join : Using « Join « SQL / MySQL

Related examples in the same category