searching multiple tables w/same fields

[psarff]

I have set up a database with two tables. The tables contain all identical fields. ex: table1 has the fields name, address, and zip code. table2 also has name, address, and zip code does anyone know how I can set up my sql statement to search both tables and pull info from both tables?

 

[piyushmittal]

Prefix each fieldname in your SELECT with the table name.<br>
<br>
For example : If your table names are CUSTOMER and EMPLOYEE.<br>
<br>
SELECT CUSTOMER.name, EMPLOYEE.name<br>
FROM CUSTOMER, EMPLOYEE<br>
WHERE CUSTOMER.id = EMPLOYEE.id;<br>
<br>
will give you the name of each employee who is also a customer.<br>
<br>
All the best.

 

[MikeLacey]

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

 

[psarff]

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

 

[bitbrain]

UNION is the way to go.<br>
<br>
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.<br>
<br>
Then, write queries against the virtual table (query). Your queries can be as sophisticated as they need to be.<br>
<br>
<br>
To get a little fancier...<br>
<br>
Add a new field in the virtual table to identify which table a given row came from. For example:<br>
<br>
select *, "a" as source from taba <br>
union select *, "b" as source from tabb<br>
<br>
will create a new column called "source" that will contain either "a" or "b"<br>
<br>
Queries against the virtual table can identify which table the rows came from.<br>
<br>

 

[calahans]

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:<br>
<br>
1) Create View below:<br>
CREATE VIEW MASTER AS SELECT * FROM TABLE_A UNION SELECT * FROM TABLE_B;<br>
<br>
2)tO QUERY:<br>
SELECT * FROM MASTER WHERE [COLUMN]=[VALUE];<br>
<br>
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.<br>
<br>
Hope this is helpful.<br>