Take the 2-minute tour ×

I need to create a table that will combine two database tables together. Both databases have a table that share a common column: core.

So I need each row to show the common "core".

Do I need to create a separate database to achieve this?

Database: hero
Table: cartons_current
| date    |
| core    |
| qty     |

Database: hero_label
Table: labels_current
| date    |
| core    |
| qty     |
share|improve this question
This is mySQL to add info –  Erik Jun 11 '12 at 20:20
Your example provides two tables that have the same column names. This contradicts what you suggest in your prose. Can you clarify if there is a typo, or what the misunderstanding is if not? –  Nathaniel Ford Jun 11 '12 at 20:27
Yes. There are two different databases & tables with the same column name. Each table has corresponding columns with different values. I need both records to show together in one table row. –  Erik Jun 11 '12 at 20:30
And is it that these databases will continue to receive data, or you want to do a one-time merge? –  Nathaniel Ford Jun 11 '12 at 20:34
add comment

1 Answer

up vote 1 down vote accepted
SELECT h.*, hl.date, hl.qty FROM hero h, hero_label hl WHERE h.core = hl.core

That will show all of the values in the H table as well as values from HL table where they both have an equal core column value. Is this what you are looking for?


Sorry i didn't see they were seperate tables. Oversight on my part.

Try this

SELECT hero.cartoons_label.*, hero_label.labels_current.date, hero_label.labels_current.qty
FROM hero.cartoons_label, hero_label.labels_current
WHERE hero.cartoons_label.core = hero_label.labels_current.core

As long as you have access to both databases and both tables that SHOULD work.

share|improve this answer
Am I creating a new database to do this or can I do this in one of the existing databases? –  Erik Jun 11 '12 at 20:26
This won't work, because the tables are in different databases. –  Nathaniel Ford Jun 11 '12 at 20:28
@Erik This query will basically take both tables and create one result that combines all the columns from both queries where they have equivalent core column values. If you want to use this as its own table you can create a view out of it. Then when querying it you would just use something like "SELECT * FROM hero_view" –  Tony318 Jun 11 '12 at 20:29
@NathanielFord added an edit. If both of those databases are MySQL that should work. I don't know if there is an effect if they are different machines. For instance if one is a local DB and the other is pulled from a server. Or if they are on seperate servers. –  Tony318 Jun 11 '12 at 20:40
@Tony318 That should work, though it is dependent on the databases having the same access parameters. Also, it doesn't solve the issue if he's looking to merge the databases, only if he wants to be able to query them both at the same time. But we need his help and clarification for that. :) –  Nathaniel Ford Jun 11 '12 at 20:46
show 1 more comment

Your Answer


