How to join search result in one query on mutil table

DBConfuser · #1 (**permalink**) 11-04-10, 13:10

Hi everyone.

I have a not difficult issue, however, I do not know how to implement it.

my case is:

I have two table. table_A, table_B,
table_a {id, name}
table_b {id, address}
what i am want to do is:

i want to find some text in both table with in column name or address.
maybe you will ask me why not create one table, sorry, there is some reason there.

let me continue my case, for example, I want to search record with text: mike.

select A.name from table_A where name like "text%"
select B.address from table_B where address like "text%"

is there a way to query both table at same time.

like:
select A.name, B.address from table_A, table_b where A.name like "text%" or B.address like "text%" and A.id != B.id;

thanks.

r937 · #2 (**permalink**) 11-04-10, 13:23

Quote:

Originally Posted by DBConfuser

is there a way to query both table at same time.

yes, with a union query

Code:

SELECT A.name AS found_column
     , 'A'    AS found_table
  FROM table_A AS A
 WHERE name LIKE 'text%'
UNION ALL
SELECT B.address 
     , 'B'
  FROM table_B AS B
 WHERE address LIKE 'text%'

DBConfuser · #3 (**permalink**) 11-04-10, 13:35

that was awesome,

and it works great.

Thanks R937.

if i want to remove the same ID record. how to do it?

sorry I am new pie for database and so greedy.

Internet Services

Web Development

Digital Marketing

Consumer Tech