×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

searching multiple tables w/same fields

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

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.

RE: searching multiple tables w/same fields

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

Mike

RE: searching multiple tables w/same fields

(OP)
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?

RE: searching multiple tables w/same fields

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.

RE: searching multiple tables w/same fields

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close