JOIN question

ChetUbetcha

Two tables, table_1 with roughly 700 items and the data is considered old. table_2 has 4,000 items with current data. I need to match the rows by "name".

I need to join table_1 (old data) with table_2 (current data) when "name" matches. BUT, if the data in any column exists in the same column, display table_2 data. If it exists in table_2 column, but not in table_2 column, display table_1 data. I have been trying all sorts of joins, but cannot get it to work correctly.

anakadote

If it exists in table_2 column, but not in table_2 column

Do you mean "If it exists in table_1 column, but not in table_2 column"?

Can you just do the join by 'name', and then use PHP to compare the results, as opposed to relying on an SQL query to do so?

ChetUbetcha

If it exists in table_1, but not in table_2, still use table_1 data. Only replace with table_2 data if it exists. Sorry

bradgrafelman

Which DBMS are you using? I'm assuming MySQL for my reply...

anakadote;10993241 wrote:
Can you just do the join by 'name', and then use PHP to compare the results, as opposed to relying on an SQL query to do so?

You could, but why bother? Heck, you could store all of the data in flat files and just use PHP to manipulate all of it... but again, why bother? :p

@: What does it mean for a column to "exist" in one table but not the other? If an item exists in both table_1 and table_2, it's going to have a value for all columns in both tables.

Is the default value NULL for the columns in table_2? In other words, by "not exist" did you really mean "contain the NULL value" ? If that's the case, then you could simply use COALESCE() on each column pair between the two tables, making sure to list the column in table_2 before the one in table_1.

EDIT: Forgot to discuss the JOIN involved; can you elaborate more on which data you're wanting? Do you want all of table_2, all of table_1, only items that are contained in both tables, or all items (e.g. items from both table_1 and table_2)?

ChetUbetcha

MySQL 5.0.37

ChetUbetcha

Yes, for those containing NULL values.

bradgrafelman

See my EDIT above in regards to what JOIN type to use if you still aren't sure, otherwise it sounds like COALESCE() is the simplest solution.