Query to find values that are the same in multiple tables
I have a project in my MySQL class, and for this project I chose my game library. The query that I am using is to find which games were developed in-house, that is where the developer is the same company as the publisher. I have tried multiple queries, but seem to be getting caught up on using column names to join the table rather than the values in the column being equal. I will describe the tables and list some queries I have tried without success. I think the issue could be resolved by using an IN but I am not sure where I should start with that. Any help would be greatly appreciated!
mysql> describe PROJ_GAME;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| GAME_ID | char(10) | NO | PRI | | |
| GAME_NAME | char(50) | YES | | NULL | |
| PUB_ID | char(10) | YES | | NULL | |
| DEV_ID | char(10) | YES | | NULL | |
| DIST_ID | char(10) | YES | | NULL | |
| PRICE | decimal(5,2) | YES | | NULL | |
| METASCORE | decimal(3,0) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
mysql> describe PROJ_DEVELOPER;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| DEV_ID | char(10) | NO | PRI | | |
| DEV_NAME | char(35) | YES | | NULL | |
| CITY | char(20) | YES | | NULL | |
| STATE | char(15) | YES | | NULL | |
| COUNTRY | char(15) | YES | | NULL | |
| YEAR_FOUNDED | decimal(4,0) | YES | | NULL | |
| CLOSED | char(3) | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
mysql> describe PROJ_PUBLISHER;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| PUB_ID | char(10) | NO | PRI | | |
| PUB_NAME | char(35) | YES | | NULL | |
| CITY | char(20) | YES | | NULL | |
| STATE | char(15) | YES | | NULL | |
| COUNTRY | char(15) | YES | | NULL | |
| YEAR_FOUNDED | decimal(4,0) | YES | | NULL | |
| CLOSED | char(3) | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
mysql> select GAME_NAME, DEV_NAME as CREATOR, PRICE, METASCORE from PROJ_GAME, PROJ_DEVELOPER, PROJ_PUBLISHER where PROJ_DEVELOPER.DEV_NAME = PROJ_PUBLISHER.PUB_NAME group by DEV_NAME;
+-----------------------------------+-----------------------+-------+-----------+
| GAME_NAME | CREATOR | PRICE | METASCORE |
+-----------------------------------+-----------------------+-------+-----------+
| Company of Heroes: Tales of Valor | 1C Company | 0.00 | 93 |
| Company of Heroes: Tales of Valor | CCP | 0.00 | 93 |
| Company of Heroes: Tales of Valor | EA | 0.00 | 93 |
mysql> select GAME_NAME, DEV_NAME as CREATOR, PRICE, METASCORE from PROJ_GAME, PROJ_DEVELOPER, PROJ_PUBLISHER where PROJ_DEVELOPER.DEV_NAME = PROJ_PUBLISHER.PUB_NAME group by GAME_NAME;
+----------------------------------------------+------------+-------+-----------+
| GAME_NAME | CREATOR | PRICE | METASCORE |
+----------------------------------------------+------------+-------+-----------+
| Age of Conan | 1C Company | 9.99 | 80 |
| Arcanum | 1C Company | 9.99 | 81 |
| Civilization IV | 1C Company | 19.99 | 94 |
| Civilization IV: Beyond the Sword | 1C Company | 19.99 | 86 |
mysql> select GAME_NAME, DEV_NAME as CREATOR, PRICE, METASCORE from PROJ_GAME, PROJ_DEVELOPER, PROJ_PUBLISHER where PROJ_DEVELOPER.DEV_NAME = PROJ_PUBLISHER.PUB_NAME group by CREATOR;
+-----------------------------------+-----------------------+-------+-----------+
| GAME_NAME | CREATOR | PRICE | METASCORE |
+-----------------------------------+-----------------------+-------+-----------+
| Company of Heroes: Tales of Valor | 1C Company | 0.00 | 93 |
| Company of Heroes: Tales of Valor | CCP | 0.00 | 93 |
| Company of Heroes: Tales of Valor | EA | 0.00 | 93 |
mysql> select DISTINCT(GAME_NAME), DEV_NAME as CREATOR, PRICE, METASCORE from PROJ_GAME, PROJ_DEVELOPER, PROJ_PUBLISHER where PROJ_DEVELOPER.DEV_NAME = PROJ_PUBLISHER.PUB_NAME group by CREATOR;
+-----------------------------------+-----------------------+-------+-----------+
| GAME_NAME | CREATOR | PRICE | METASCORE |
+-----------------------------------+-----------------------+-------+-----------+
| Company of Heroes: Tales of Valor | 1C Company | 0.00 | 93 |
| Company of Heroes: Tales of Valor | CCP | 0.00 | 93 |
mysql> select DISTINCT(GAME_NAME), DEV_NAME as CREATOR, PRICE, METASCORE from PROJ_GAME, PROJ_DEVELOPER, PROJ_PUBLISHER where PROJ_DEVELOPER.DEV_NAME = PROJ_PUBLISHER.PUB_NAME group by GAME_NAME;
+----------------------------------------------+------------+-------+-----------+
| GAME_NAME | CREATOR | PRICE | METASCORE |
+----------------------------------------------+------------+-------+-----------+
| Age of Conan | 1C Company | 9.99 | 80 |
| Arcanum | 1C Company | 9.99 | 81 |
mysql> select GAME_NAME, DEV_NAME as CREATOR, PRICE, METASCORE from PROJ_GAME, PROJ_DEVELOPER, PROJ_PUBLISHER where PROJ_DEVELOPER.DEV_ID = PROJ_GAME.DEV_ID and PROJ_GAME.PUB_ID = PROJ_DEVELOPER.DEV_ID and DEVELOPER.DEV_NAME = PUBLISHER.PUB_NAME group by GAME_NAME;
ERROR 1054 (42S22): Unknown column 'DEVELOPER.DEV_NAME' in 'where clause'
mysql> select GAME_NAME, DEV_NAME as CREATOR, PRICE, METASCORE from PROJ_GAME, PROJ_DEVELOPER, PROJ_PUBLISHER where PROJ_DEVELOPER.DEV_ID = PROJ_GAME.DEV_ID and PROJ_GAME.PUB_ID = PROJ_DEVELOPER.DEV_ID and PROJ_DEVELOPER.DEV_NAME = PROJ_PUBLISHER.PUB_NAME group by GAME_NAME;
Empty set (0.00 sec)