searching multiple tables w/same fields
searching multiple tables w/same fields
(OP)
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?
RE: searching multiple tables w/same fields
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.
RE: searching multiple tables w/same fields
Mike
RE: searching multiple tables w/same fields
"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?
RE: searching multiple tables w/same fields
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.
RE: searching multiple tables w/same fields
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.