how to search data in 4 different tables

Question

0.00/5 (No votes)

See more:

I would like to know on how to search for a data in different table but with only one query if it is possible, example;

SQL

select * from table1,table2,table3 WHERE id="$searctext";

is this query applicable or there's any better query?

for your information, those 4 tables have all the same attributes, for example, table1 has id name address.

so does the table 2,3,4 but did not relate to one another, so im going to search for example by id, I input it as 001, and what the query do is it search through all 4 tables to obtain the id and all information about the id.

Please help me with this, all help are much appreciated :)

Posted 4-Oct-11 16:08pm

silverantes

Updated 4-Oct-11 16:09pm

walterhevedeich

v3

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mehdi Gholam · Answer 1 · 2011-10-04T18:38:00

Solution 1

You can use the following query provided the columns match exactly :

SQL

select * from table1 where id = 'somevalue' 
union 
select * from table2 where id = 'somevalue'
union
...

Posted 4-Oct-11 18:38pm

Mehdi Gholam

Graham Breach · Answer 2 · 2011-10-04T22:57:00

Solution 2

I agree with Mehdi's answer, but I'll add this in case you need to know which table each row is coming from:

SELECT *, 'table1' AS tbl FROM table1 WHERE id='$searchtext'
UNION
SELECT *, 'table2' AS tbl FROM table2 WHERE id='$searchtext'
UNION
...

Posted 4-Oct-11 22:57pm

Graham Breach

Bala Selvanayagam · Answer 3 · 2011-10-05T02:06:00

I assume you will have the same id value in multi0ple tables ? and have to use UNION ALL

The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL.

SQL

SELECT *  FROM table1 WHERE id='$searchtext'
UNION ALL
SELECT *  FROM table2 WHERE id='$searchtext'
UNION ALL
...

silverantes · Answer 4 · 2011-10-05T15:39:00

Solution 4

Thank you guys,all your solution helped me a lot,you guys indeed an expert :)

Posted 5-Oct-11 15:39pm