mysql> select * from poke30; +-----------+------------+--------+--------+----------------+ | PokedexID | Name | Height | Weight | BaseExperience | +-----------+------------+--------+--------+----------------+ | 1 | Bulbasaur | 7 | 300 | 64 | | 2 | Ivysaur | 10 | 130 | 141 | | 3 | Venusaur | 20 | 1000 | 208 | | 4 | Charmander | 6 | 85 | 65 | | 5 | Charmeleon | 11 | 190 | 142 | | 6 | Charizard | 17 | 905 | 209 | | 7 | Squirtle | 5 | 90 | 66 | | 8 | Wartortle | 10 | 225 | 143 | | 9 | Blastoise | 16 | 855 | 210 | | 10 | Caterpie | 3 | 29 | 53 | | 11 | Metapod | 7 | 99 | 72 | | 12 | Butterfree | 11 | 320 | 160 | | 13 | Weedle | 3 | 32 | 52 | | 14 | Kakuna | 6 | 100 | 71 | | 15 | Beedrill | 10 | 295 | 159 | | 16 | Pidgey | 3 | 18 | 55 | | 17 | Pidgeotto | 11 | 300 | 113 | | 18 | Pidgeot | 15 | 395 | 172 | | 19 | Rattata | 3 | 35 | 57 | | 20 | Raticate | 7 | 185 | 116 | | 21 | Spearow | 3 | 20 | 58 | | 22 | Fearow | 12 | 380 | 162 | | 23 | Ekans | 20 | 69 | 62 | | 24 | Arbok | 35 | 650 | 147 | | 25 | Pikachu | 4 | 60 | 82 | | 26 | Raichu | 8 | 300 | 122 | | 27 | Sandshrew | 6 | 120 | 93 | | 28 | Sandslash | 10 | 295 | 163 | | 29 | Nidoran♀ | 4 | 70 | 59 | | 30 | Nidorina | 8 | 200 | 117 | +-----------+------------+--------+--------+----------------+ 30 rows in set (0.00 sec) mysql> SELECT Name, -> CASE -> WHEN weight >= 100 THEN 'Heavy' -> ELSE 'Light' -> END AS WT_Comment -> FROM poke30 -> ; +------------+------------+ | Name | WT_Comment | +------------+------------+ | Bulbasaur | Heavy | | Ivysaur | Heavy | | Venusaur | Heavy | | Charmander | Light | | Charmeleon | Heavy | | Charizard | Heavy | | Squirtle | Light | | Wartortle | Heavy | | Blastoise | Heavy | | Caterpie | Light | | Metapod | Light | | Butterfree | Heavy | | Weedle | Light | | Kakuna | Heavy | | Beedrill | Heavy | | Pidgey | Light | | Pidgeotto | Heavy | | Pidgeot | Heavy | | Rattata | Light | | Raticate | Heavy | | Spearow | Light | | Fearow | Heavy | | Ekans | Light | | Arbok | Heavy | | Pikachu | Light | | Raichu | Heavy | | Sandshrew | Heavy | | Sandslash | Heavy | | Nidoran♀ | Light | | Nidorina | Heavy | +------------+------------+ 30 rows in set (0.00 sec) mysql> SELECT Name, -> CASE -> WHEN weight >= 100 THEN 'Heavy' -> WHEN weight >= 50 THEN 'Midsize' -> ELSE 'Light' -> END AS WT_Comment -> FROM poke30 -> ; +------------+------------+ | Name | WT_Comment | +------------+------------+ | Bulbasaur | Heavy | | Ivysaur | Heavy | | Venusaur | Heavy | | Charmander | Midsize | | Charmeleon | Heavy | | Charizard | Heavy | | Squirtle | Midsize | | Wartortle | Heavy | | Blastoise | Heavy | | Caterpie | Light | | Metapod | Midsize | | Butterfree | Heavy | | Weedle | Light | | Kakuna | Heavy | | Beedrill | Heavy | | Pidgey | Light | | Pidgeotto | Heavy | | Pidgeot | Heavy | | Rattata | Light | | Raticate | Heavy | | Spearow | Light | | Fearow | Heavy | | Ekans | Midsize | | Arbok | Heavy | | Pikachu | Midsize | | Raichu | Heavy | | Sandshrew | Heavy | | Sandslash | Heavy | | Nidoran♀ | Midsize | | Nidorina | Heavy | +------------+------------+ 30 rows in set (0.00 sec) mysql> mysql> SELECT Name, -> CASE -> WHEN weight >= 50 THEN 'Midsize' -> WHEN weight >= 100 THEN 'Heavy' -> ELSE 'Light' -> END AS WT_Comment -> FROM poke30 -> ; +------------+------------+ | Name | WT_Comment | +------------+------------+ | Bulbasaur | Midsize | | Ivysaur | Midsize | | Venusaur | Midsize | | Charmander | Midsize | | Charmeleon | Midsize | | Charizard | Midsize | | Squirtle | Midsize | | Wartortle | Midsize | | Blastoise | Midsize | | Caterpie | Light | | Metapod | Midsize | | Butterfree | Midsize | | Weedle | Light | | Kakuna | Midsize | | Beedrill | Midsize | | Pidgey | Light | | Pidgeotto | Midsize | | Pidgeot | Midsize | | Rattata | Light | | Raticate | Midsize | | Spearow | Light | | Fearow | Midsize | | Ekans | Midsize | | Arbok | Midsize | | Pikachu | Midsize | | Raichu | Midsize | | Sandshrew | Midsize | | Sandslash | Midsize | | Nidoran♀ | Midsize | | Nidorina | Midsize | +------------+------------+ 30 rows in set (0.00 sec) mysql> SELECT Name, -> CASE -> WHEN weight >= 50 and weight <100 THEN 'Midsize' -> WHEN weight >= 100 THEN 'Heavy' -> ELSE 'Light' -> END AS WT_Comment -> FROM poke30 -> ; +------------+------------+ | Name | WT_Comment | +------------+------------+ | Bulbasaur | Heavy | | Ivysaur | Heavy | | Venusaur | Heavy | | Charmander | Midsize | | Charmeleon | Heavy | | Charizard | Heavy | | Squirtle | Midsize | | Wartortle | Heavy | | Blastoise | Heavy | | Caterpie | Light | | Metapod | Midsize | | Butterfree | Heavy | | Weedle | Light | | Kakuna | Heavy | | Beedrill | Heavy | | Pidgey | Light | | Pidgeotto | Heavy | | Pidgeot | Heavy | | Rattata | Light | | Raticate | Heavy | | Spearow | Light | | Fearow | Heavy | | Ekans | Midsize | | Arbok | Heavy | | Pikachu | Midsize | | Raichu | Heavy | | Sandshrew | Heavy | | Sandslash | Heavy | | Nidoran♀ | Midsize | | Nidorina | Heavy | +------------+------------+ 30 rows in set (0.00 sec) mysql> SELECT Name -> FROM poke30 -> WHERE weight < CASE -> when height < 10 THEN 30 -> ELSE 100 -> END -> ; +----------+ | Name | +----------+ | Caterpie | | Pidgey | | Spearow | | Ekans | +----------+ 4 rows in set (0.00 sec) mysql> select * from poke30; +-----------+------------+--------+--------+----------------+ | PokedexID | Name | Height | Weight | BaseExperience | +-----------+------------+--------+--------+----------------+ | 1 | Bulbasaur | 7 | 300 | 64 | | 2 | Ivysaur | 10 | 130 | 141 | | 3 | Venusaur | 20 | 1000 | 208 | | 4 | Charmander | 6 | 85 | 65 | | 5 | Charmeleon | 11 | 190 | 142 | | 6 | Charizard | 17 | 905 | 209 | | 7 | Squirtle | 5 | 90 | 66 | | 8 | Wartortle | 10 | 225 | 143 | | 9 | Blastoise | 16 | 855 | 210 | | 10 | Caterpie | 3 | 29 | 53 | | 11 | Metapod | 7 | 99 | 72 | | 12 | Butterfree | 11 | 320 | 160 | | 13 | Weedle | 3 | 32 | 52 | | 14 | Kakuna | 6 | 100 | 71 | | 15 | Beedrill | 10 | 295 | 159 | | 16 | Pidgey | 3 | 18 | 55 | | 17 | Pidgeotto | 11 | 300 | 113 | | 18 | Pidgeot | 15 | 395 | 172 | | 19 | Rattata | 3 | 35 | 57 | | 20 | Raticate | 7 | 185 | 116 | | 21 | Spearow | 3 | 20 | 58 | | 22 | Fearow | 12 | 380 | 162 | | 23 | Ekans | 20 | 69 | 62 | | 24 | Arbok | 35 | 650 | 147 | | 25 | Pikachu | 4 | 60 | 82 | | 26 | Raichu | 8 | 300 | 122 | | 27 | Sandshrew | 6 | 120 | 93 | | 28 | Sandslash | 10 | 295 | 163 | | 29 | Nidoran♀ | 4 | 70 | 59 | | 30 | Nidorina | 8 | 200 | 117 | +-----------+------------+--------+--------+----------------+ 30 rows in set (0.00 sec) mysql> SELECT Name, weight, height -> FROM poke30 -> WHERE weight < CASE -> when height < 10 THEN 30 -> ELSE 100 -> END -> ; +----------+--------+--------+ | Name | weight | height | +----------+--------+--------+ | Caterpie | 29 | 3 | | Pidgey | 18 | 3 | | Spearow | 20 | 3 | | Ekans | 69 | 20 | +----------+--------+--------+ 4 rows in set (0.00 sec) mysql> CREATE VIEW wcomment AS ( -> SELECT Name, -> CASE -> WHEN weight >= 50 and weight <100 THEN 'Midsize' -> WHEN weight >= 100 THEN 'Heavy' -> ELSE 'Light' -> END AS WT_Comment -> FROM poke30 -> ) -> ; Query OK, 0 rows affected (0.01 sec) mysql> select * from wcomment; +------------+------------+ | Name | WT_Comment | +------------+------------+ | Bulbasaur | Heavy | | Ivysaur | Heavy | | Venusaur | Heavy | | Charmander | Midsize | | Charmeleon | Heavy | | Charizard | Heavy | | Squirtle | Midsize | | Wartortle | Heavy | | Blastoise | Heavy | | Caterpie | Light | | Metapod | Midsize | | Butterfree | Heavy | | Weedle | Light | | Kakuna | Heavy | | Beedrill | Heavy | | Pidgey | Light | | Pidgeotto | Heavy | | Pidgeot | Heavy | | Rattata | Light | | Raticate | Heavy | | Spearow | Light | | Fearow | Heavy | | Ekans | Midsize | | Arbok | Heavy | | Pikachu | Midsize | | Raichu | Heavy | | Sandshrew | Heavy | | Sandslash | Heavy | | Nidoran♀ | Midsize | | Nidorina | Heavy | +------------+------------+ 30 rows in set (0.01 sec) mysql> select * from wcomment where w_comment = 'Midsize'; ERROR 1054 (42S22): Unknown column 'w_comment' in 'where clause' mysql> select * from wcomment where wt_comment = 'Midsize'; +------------+------------+ | Name | WT_Comment | +------------+------------+ | Charmander | Midsize | | Squirtle | Midsize | | Metapod | Midsize | | Ekans | Midsize | | Pikachu | Midsize | | Nidoran♀ | Midsize | +------------+------------+ 6 rows in set (0.01 sec) mysql> mysql> CREATE Table weightc AS ( -> SELECT Name, -> CASE -> WHEN weight >= 50 and weight <100 THEN 'Midsize' -> WHEN weight >= 100 THEN 'Heavy' -> ELSE 'Light' -> END AS WT_Comment -> FROM poke30 -> ) -> ; Query OK, 30 rows affected (0.01 sec) Records: 30 Duplicates: 0 Warnings: 0 mysql> mysql> select * from weightc; +------------+------------+ | Name | WT_Comment | +------------+------------+ | Bulbasaur | Heavy | | Ivysaur | Heavy | | Venusaur | Heavy | | Charmander | Midsize | | Charmeleon | Heavy | | Charizard | Heavy | | Squirtle | Midsize | | Wartortle | Heavy | | Blastoise | Heavy | | Caterpie | Light | | Metapod | Midsize | | Butterfree | Heavy | | Weedle | Light | | Kakuna | Heavy | | Beedrill | Heavy | | Pidgey | Light | | Pidgeotto | Heavy | | Pidgeot | Heavy | | Rattata | Light | | Raticate | Heavy | | Spearow | Light | | Fearow | Heavy | | Ekans | Midsize | | Arbok | Heavy | | Pikachu | Midsize | | Raichu | Heavy | | Sandshrew | Heavy | | Sandslash | Heavy | | Nidoran♀ | Midsize | | Nidorina | Heavy | +------------+------------+ 30 rows in set (0.00 sec) mysql> select * from weightc where name = 'Bulbasaur'; +-----------+------------+ | Name | WT_Comment | +-----------+------------+ | Bulbasaur | Heavy | +-----------+------------+ 1 row in set (0.00 sec) mysql> select * from wcomment where name = 'Bulbasaur'; +-----------+------------+ | Name | WT_Comment | +-----------+------------+ | Bulbasaur | Heavy | +-----------+------------+ 1 row in set (0.01 sec) mysql> update poke30 -> set weight = 69 where name = 'Bulbasaur'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from weightc where name = 'Bulbasaur'; +-----------+------------+ | Name | WT_Comment | +-----------+------------+ | Bulbasaur | Heavy | +-----------+------------+ 1 row in set (0.00 sec) mysql> select * from wcomment where name = 'Bulbasaur'; +-----------+------------+ | Name | WT_Comment | +-----------+------------+ | Bulbasaur | Midsize | +-----------+------------+ 1 row in set (0.00 sec) mysql> use tr Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> create table bob(id int primary key, val varchar(10)); Query OK, 0 rows affected (0.01 sec) mysql> create table snocones(id int primary key, conetype varchar(10)); Query OK, 0 rows affected (0.01 sec) mysql> insert into bob values(1, 'bob'), (2, 'bob'), (3, 'not bob'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> insert into snocones values (1, 'pine'), (2, 'oak'), (3, 'sugar'); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> create view absurd as (select b.val, s.type from bob b, snocones s, where b.id = s.id); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'where b.id = s.id)' at line 1 mysql> create view absurd as (select b.val, s.type from bob b, snocones s where b.id = s.id); ERROR 1054 (42S22): Unknown column 's.type' in 'field list' mysql> create view absurd as (select b.val, s.conetype from bob b, snocones s where b.id = s.id); Query OK, 0 rows affected (0.01 sec) mysql> select * fron absurd; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'fron absurd' at line 1 mysql> select * from absurd; +---------+----------+ | val | conetype | +---------+----------+ | bob | pine | | bob | oak | | not bob | sugar | +---------+----------+ 3 rows in set (0.00 sec) mysql> drop table bob; Query OK, 0 rows affected (0.00 sec) mysql> show tables; +--------------+ | Tables_in_tr | +--------------+ | absurd | | snocones | | ttest | +--------------+ 3 rows in set (0.00 sec) mysql> select * from absurd; ERROR 1356 (HY000): View 'tr.absurd' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysql> create table bob(id int primary key, val varchar(10)); Query OK, 0 rows affected (0.01 sec) mysql> insert into bob values(1, 'bob'), (2, 'bob'), (5, 'not bob'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from absurd; +------+----------+ | val | conetype | +------+----------+ | bob | pine | | bob | oak | +------+----------+ 2 rows in set (0.00 sec) mysql> update absurd set conetype = 'sugar' where conetype = 'oak'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from absurd; +------+----------+ | val | conetype | +------+----------+ | bob | pine | | bob | sugar | +------+----------+ 2 rows in set (0.00 sec) mysql> select * from snocones; +----+----------+ | id | conetype | +----+----------+ | 1 | pine | | 2 | sugar | | 3 | sugar | +----+----------+ 3 rows in set (0.00 sec) mysql> create view analytics as (select conetype, count(*) as num from snocones); Query OK, 0 rows affected (0.00 sec) mysql> select * from analytics; +----------+-----+ | conetype | num | +----------+-----+ | pine | 3 | +----------+-----+ 1 row in set (0.00 sec) mysql> create view analytics as (select conetype, count(*) as num from snocones group by conetype); ERROR 1050 (42S01): Table 'analytics' already exists mysql> drop view analytics; Query OK, 0 rows affected (0.00 sec) mysql> create view analytics as (select conetype, count(*) as num from snocones group by conetype); Query OK, 0 rows affected (0.01 sec) mysql> select * from analytics; +----------+-----+ | conetype | num | +----------+-----+ | pine | 1 | | sugar | 2 | +----------+-----+ 2 rows in set (0.00 sec) mysql> update analytics set num = 4 where num = 2; ERROR 1288 (HY000): The target table analytics of the UPDATE is not updatable mysql> update analytics set conetype = 'oak' where conetype = 'sugar'; ERROR 1288 (HY000): The target table analytics of the UPDATE is not updatable mysql> select * from snocones; +----+----------+ | id | conetype | +----+----------+ | 1 | pine | | 2 | sugar | | 3 | sugar | +----+----------+ 3 rows in set (0.00 sec) mysql> select * from snocones; +----+----------+ | id | conetype | +----+----------+ | 1 | pine | | 2 | sugar | | 3 | sugar | | 4 | oak | +----+----------+ 4 rows in set (0.00 sec) mysql> select * from snocones; +----+----------+ | id | conetype | +----+----------+ | 1 | pine | | 2 | sugar | | 3 | sugar | | 4 | oak | +----+----------+ 4 rows in set (0.00 sec) mysql> insert into snocones values(7, 'steel'); Query OK, 1 row affected (0.01 sec) mysql> select * from snocones; +----+----------+ | id | conetype | +----+----------+ | 1 | pine | | 2 | sugar | | 3 | sugar | | 4 | oak | | 7 | steel | +----+----------+ 5 rows in set (0.00 sec) mysql> insert into snocones values(5, 'steel'); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into snocones values(5, 'steel'); ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY' mysql> insert into snocones values(6, 'steel'); Query OK, 1 row affected (7.38 sec) mysql> select * from snocones; +----+----------+ | id | conetype | +----+----------+ | 1 | pine | | 2 | sugar | | 3 | sugar | | 4 | oak | | 5 | waffle | | 6 | steel | | 7 | steel | +----+----------+ 7 rows in set (0.00 sec) mysql> start transaction -> insert into snocones values(8,'cotton'); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'insert into snocones values(8,'cotton')' at line 2 mysql> start transaction; insert into snocones values(8,'cotton'); Query OK, 0 rows affected (0.00 sec) Query OK, 1 row affected (0.00 sec) mysql> insert into snocones values(9,'cotton'); ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction mysql> select * from snocones; +----+----------+ | id | conetype | +----+----------+ | 1 | pine | | 2 | sugar | | 3 | sugar | | 4 | oak | | 5 | waffle | | 6 | steel | | 7 | steel | +----+----------+ 7 rows in set (0.00 sec) mysql> select * from snocones; +----+----------+ | id | conetype | +----+----------+ | 1 | pine | | 2 | sugar | | 3 | sugar | | 4 | oak | | 5 | waffle | | 6 | steel | | 7 | steel | +----+----------+ 7 rows in set (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> select * from snocones; +----+----------+ | id | conetype | +----+----------+ | 1 | pine | | 2 | sugar | | 3 | sugar | | 4 | oak | | 5 | waffle | | 6 | steel | | 7 | steel | | 8 | staples | | 9 | paste | +----+----------+ 9 rows in set (0.00 sec) mysql> use pokemon; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from wcomment; +------------+------------+ | Name | WT_Comment | +------------+------------+ | Bulbasaur | Midsize | | Ivysaur | Heavy | | Venusaur | Heavy | | Charmander | Midsize | | Charmeleon | Heavy | | Charizard | Heavy | | Squirtle | Midsize | | Wartortle | Heavy | | Blastoise | Heavy | | Caterpie | Light | | Metapod | Midsize | | Butterfree | Heavy | | Weedle | Light | | Kakuna | Heavy | | Beedrill | Heavy | | Pidgey | Light | | Pidgeotto | Heavy | | Pidgeot | Heavy | | Rattata | Light | | Raticate | Heavy | | Spearow | Light | | Fearow | Heavy | | Ekans | Midsize | | Arbok | Heavy | | Pikachu | Midsize | | Raichu | Heavy | | Sandshrew | Heavy | | Sandslash | Heavy | | Nidoran♀ | Midsize | | Nidorina | Heavy | +------------+------------+ 30 rows in set (0.00 sec) mysql> mysql> CREATE VIEW hcomment AS ( -> SELECT Name, -> CASE -> WHEN height >= 10 THEN 'tall' -> ELSE 'short' -> END AS HT_Comment -> FROM poke30 -> ) -> ; Query OK, 0 rows affected (0.00 sec) mysql> sleect * from hcomment; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'sleect * from hcomment' at line 1 mysql> select * from hcomment; +------------+------------+ | Name | HT_Comment | +------------+------------+ | Bulbasaur | short | | Ivysaur | tall | | Venusaur | tall | | Charmander | short | | Charmeleon | tall | | Charizard | tall | | Squirtle | short | | Wartortle | tall | | Blastoise | tall | | Caterpie | short | | Metapod | short | | Butterfree | tall | | Weedle | short | | Kakuna | short | | Beedrill | tall | | Pidgey | short | | Pidgeotto | tall | | Pidgeot | tall | | Rattata | short | | Raticate | short | | Spearow | short | | Fearow | tall | | Ekans | tall | | Arbok | tall | | Pikachu | short | | Raichu | short | | Sandshrew | short | | Sandslash | tall | | Nidoran♀ | short | | Nidorina | short | +------------+------------+ 30 rows in set (0.00 sec) mysql> SELECT * -> FROM (select * from wcomment where wt_comment= 'Heavy') -> LEFT JOIN -> (select * from hcomment where ht_comment = 'short') -> USING (Name) -> ; ERROR 1248 (42000): Every derived table must have its own alias mysql> mysql> SELECT * -> FROM (select * from wcomment where wt_comment= 'Heavy') z -> LEFT JOIN -> (select * from hcomment where ht_comment = 'short') w -> USING (Name) -> ; +------------+------------+------------+ | Name | WT_Comment | HT_Comment | +------------+------------+------------+ | Kakuna | Heavy | short | | Raticate | Heavy | short | | Raichu | Heavy | short | | Sandshrew | Heavy | short | | Nidorina | Heavy | short | | Ivysaur | Heavy | NULL | | Venusaur | Heavy | NULL | | Charmeleon | Heavy | NULL | | Charizard | Heavy | NULL | | Wartortle | Heavy | NULL | | Blastoise | Heavy | NULL | | Butterfree | Heavy | NULL | | Beedrill | Heavy | NULL | | Pidgeotto | Heavy | NULL | | Pidgeot | Heavy | NULL | | Fearow | Heavy | NULL | | Arbok | Heavy | NULL | | Sandslash | Heavy | NULL | +------------+------------+------------+ 18 rows in set (0.00 sec) mysql> select * from hcomment where ht_comment = 'short'; +------------+------------+ | Name | HT_Comment | +------------+------------+ | Bulbasaur | short | | Charmander | short | | Squirtle | short | | Caterpie | short | | Metapod | short | | Weedle | short | | Kakuna | short | | Pidgey | short | | Rattata | short | | Raticate | short | | Spearow | short | | Pikachu | short | | Raichu | short | | Sandshrew | short | | Nidoran♀ | short | | Nidorina | short | +------------+------------+ 16 rows in set (0.00 sec) mysql> SELECT * -> FROM (select * from wcomment where wt_comment= 'Heavy') z -> RIGHT JOIN -> (select * from hcomment where ht_comment = 'short') w -> USING (Name) -> ; +------------+------------+------------+ | Name | HT_Comment | WT_Comment | +------------+------------+------------+ | Kakuna | short | Heavy | | Raticate | short | Heavy | | Raichu | short | Heavy | | Sandshrew | short | Heavy | | Nidorina | short | Heavy | | Bulbasaur | short | NULL | | Charmander | short | NULL | | Squirtle | short | NULL | | Caterpie | short | NULL | | Metapod | short | NULL | | Weedle | short | NULL | | Pidgey | short | NULL | | Rattata | short | NULL | | Spearow | short | NULL | | Pikachu | short | NULL | | Nidoran♀ | short | NULL | +------------+------------+------------+ 16 rows in set (0.00 sec) mysql>