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?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
searching multiple tables w/same fields
- Thread starter psarff
- Start date
piyushmittal
MIS
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.
<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.
- Thread starter
- #4
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>
<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>
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>
<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>
- Status
Similar threads
- Locked
- Question
- Locked
- Question