How do I combine rows by ID?

[appletree]

I have two SQL tables and I want to merge them into one.

I want to merge based on the row ID and Z-ID variables.

Example:


Table 1:

ID: APPLES:
000001 TEXT-AAA
000002 TEXT-BBB
000003 TEXT-CCC
000004 TEXT-DDD
000005 TEXT-EEE
000085 TEXT-RRR


Table 2:

Z-ID: POTATOES:
000001 DIFFERENT-AAA
000002 DIFFERENT-BBB
000004 DIFFERENT-CCC
000075 DIFFERENT-TTT



What Im trying to make the tables output:

ID: POTATOES: APPLES:
000001 DIFFERENT-AAA TEXT-AAA
000002 DIFFERENT-BBB TEXT-BBB
000003 TEXT-CCC
000004 DIFFERENT-CCC TEXT-DDD
000005 TEXT-EEE
000075 DIFFERENT-TTT
000085 TEXT-RRR

[tirna]

Maybe try this:


select id, potatoes, apples
from table1, table2
where id = z-id

[NogDog]

I think you may need to do an outer join.

SELECT MAX(id, z_id) AS the_id, potatoes, apples
FROM table_1 OUTER JOIN table_2 on id = z_id

[Stuck_Process]

tirna answer works fine, and is actually recommended. Only remember that it is good practice to always name were the columns come from. So:


select table1.id, table2.potatoes, table1.apples
from table1, table2
where table1.id = table2.z-id


Any type of join is a very very costly operation, avoid it if you can. If it's doable by simply comparing IDs and or by using a view that is always preferable, since you get lower database load and shorter execution times.

[NogDog]

tirna answer works fine, and is actually recommended....

I do not see how doing a (implied) inner join will give the desired results, as Tirna's query would leave out ID's 3, 5, 75, and 85 which are not in both tables. :confused:

[NogDog]

PS: As MySQL does not support full outer joins, I found this work-around (plus the use of the COALESCE() function):

SELECT COALESCE(id, z_id) AS the_id, apples, potatoes
FROM apples LEFT JOIN potatoes ON id=z_id
UNION
SELECT COALESCE(id, z_id) AS the_id, apples, potatoes
FROM apples RIGHT JOIN potatoes ON id = z_id

[appletree]

Both methods worked for me.

Thanks!!!!!!!!!!!!