Prefix each fieldname in your SELECT with the table name.

For example : If your table names are CUSTOMER and EMPLOYEE.

SELECT CUSTOMER.name, EMPLOYEE.name
FROM CUSTOMER, EMPLOYEE
WHERE CUSTOMER.id = EMPLOYEE.id;

will give you the name of each employee who is also a customer.

All the best.

Also - have a look at the UNION keyword which joins two queries together to return one set of results.

Mike

I went with the union statement and it sort of works
"select * from table1 union select * from table2"
the problem is that it will list what i searched for at the top and then dump all of table1 just below! any suggestions?

UNION is the way to go.

Create a query (a.k.a. virtual table / view) with a simple UNION statement. I.e. all the UNION does is combine the two tables as in the previous example.

Then, write queries against the virtual table (query). Your queries can be as sophisticated as they need to be.


To get a little fancier...

Add a new field in the virtual table to identify which table a given row came from. For example:

select *, "a" as source from taba
union select *, "b" as source from tabb

will create a new column called "source" that will contain either "a" or "b"

Queries against the virtual table can identify which table the rows came from.

I would suggest an approach similar to bitbrain. From the way your original post reads, I presume that you have two table containing the same type of data, i.e. two customer tables. You are looking to search for a 'customer' (for want of an example) over the two tables. I would approach as follows:

1) Create View below:
CREATE VIEW MASTER AS SELECT * FROM TABLE_A UNION SELECT * FROM TABLE_B;

2)tO QUERY:
SELECT * FROM MASTER WHERE [COLUMN]=[VALUE];

However, if these tables are identical they should be one table, with a good index(es) on it. The reason for your problems may be the design of your database. Large database design should not be approached without a good knowledge of RDA.

Hope this is helpful.