MySQL Forums :: Newbie :: Query to find values that are the same in multiple tables


Advanced Search

Query to find values that are the same in multiple tables
Posted by: Chris Sanderson ()
Date: November 28, 2010 01:48PM

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)

Options: ReplyQuote


Subject Written By Posted
Query to find values that are the same in multiple tables Chris Sanderson 11/28/2010 01:48PM
Re: Query to find values that are the same in multiple tables Rick James 11/29/2010 08:26PM
Re: Query to find values that are the same in multiple tables Chris Sanderson 11/29/2010 11:59PM
Re: Query to find values that are the same in multiple tables Rick James 11/30/2010 12:18AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.