joining tables

[cvcvcvcv]

Hi:

I want to join a table1 with table2 in a common key field which should contain all rows from table1 and also rows from table2 which do not match the key field.

For example:

Table1

1 a
2 b
3 c

Table2

1 a
4 d
5 e

The join should show:

1 a
2 b
3 c
4 d
5 e

How is this done with a single query? Or do we need to do it with multiple queries storing results in temporary tables?

Chris Vasanth

 

[ToeShot]

I am not sure if this is exactly what your looking for but It worked great for me for combining information from 2 tables. Exert From the MySql 3.23.41 HTML Manual

3.5.7 Searching on Two Keys

MySQL doesn't yet optimize when you search on two different keys combined with OR (Searching on one key with different OR parts is optimized quite good):

SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1'
OR field2_index = '1'

The reason is that we haven't yet had time to come up with an efficient way to handle this in the general case. (The AND handling is, in comparison, now completely general and works very well).

For the moment you can solve this very efficiently by using a TEMPORARY table. This type of optimization is also very good if you are using very complicated queries where the SQL server does the optimizations in the wrong order.

CREATE TEMPORARY TABLE tmp
SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1';
INSERT INTO tmp
SELECT field1_index, field2_index FROM test_table WHERE field2_index = '1';
SELECT * from tmp;
DROP TABLE tmp;

The above way to solve this query is in effect an UNION of two queries.

 

[Broccoli2]

To do it in one select you need a union query. These aren't supported in MySQL 3.23 but they are in the new 4.0 release.

-Rob