用于seacher的两个非关系表的SQL语句

Question

I'm doing a searcher for my webpage and i'm facing a little issue, i want to get the info from two non relationals tables but the data is not returning the way i want

table 1

ID |FNAME  |LNAME | STATE  | CITY 
------------------------------
1  |xxxxx1 |xxxxx1| xxxx1  | xx1     
2  |xxxxx2 |xxxxx2| toronto| xx2
3  |xxxxx3 |xxxxx3| xxxx3  | yy3
4  |zzzzz3 |zzzzz3| toronto| yy3

table 2

ID |NAME   | STATE  | CITY 
---------------------
1  |yyyyy1 | yyyy1  | yy1     
2  |yyyyy2 | yyyy2  | yy2
3  |yyyyy3 | toronto| yy3

currently i have

SELECT
    e.id_client, e.fname_client, e.city_client, e.state_client , m.id_client, m.fname_client, m.lname_client, m.state_client, m.city_client 
FROM 
    empresas e 
CROSS JOIN 
    medicos m 
WHERE 
    e.fname_client LIKE :busqueda 
OR 
    e.city_client LIKE :busqueda 
OR  
    m.fname_client LIKE :busqueda 
OR 
    m.lname_client LIKE :busqueda 
OR 
    m.state_client LIKE :busqueda 
OR 
    m.city_client LIKE :busqueda

this displays the result like this

ID| FNAME |LNAME | STATE  | CITY |ID|FNAME  |LNAME | STATE  | CITY 
3 |yyyyy3 |      | yyyy3  | yy3  |3 |xxxxx3 |xxxxx3| xxxx3  | yy3

but i want it like this

ID|FNAME  |LNAME | STATE  | CITY 
3 |xxxxx3 |xxxxx3| xxxx3  | yy3
3 |yyyyy3 |      | yyyy3  | yy3

EDIT:

with the UNION asnwer i get the data with the format i want, but is showing only the results from one table when i search for commons values, for example:

if i type "Toronto", this must shows

    ID|FNAME  |LNAME | STATE    | CITY 
    2 |xxxxx3 |xxxxx3| toronto  | yy3 <- doctor
    3 |yyyyy3 |      | toronto  | yy3 <- organization
    4 |zzzzz3 |zzzzz3| toronto  | yy3

but is only showing this

    ID|FNAME  |LNAME | STATE  | CITY 
    2 |xxxxx3 |xxxxx3| toronto  | yy3 <- doctor
    4 |zzzzz3 |zzzzz3| toronto  | yy3 <- doctor

Answer 1

I guess you want to concatenate the two tables. Then...

select 
    id, fname, lname, state, city
from 
    table1
where
    <your where condition here>
union all
select 
    id, fname, lname, state, city
from 
    table2
where
    <your other where condition here>

EDIT

If you have:

SQL> select * from table1 order by id;
 id |   fname    |   lname    |   state    |    city    
----+------------+------------+------------+------------
  1 | xxxxx1     | xxxxx1     | xxxx1      | xx1       
  2 | xxxxx2     | xxxxx2     | toronto    | xx2       
  3 | xxxxx3     | xxxxx3     | xxxx3      | xx3       
  4 | zzzzz3     | zzzzz3     | toronto    | yy3  
SQL> select * from table2 order by id;
 id |    name    |   state    |    city    
----+------------+------------+------------
  1 | yyyyy1     | yyyy1      | yy1       
  2 | yyyyy2     | yyyy2      | yy2       
  3 | yyyyy3     | toronto    | yy3  

then:

select 
    id, fname, lname, state, city
from 
    table1
where
    state='toronto'
union all
select 
    id, name as fname, NULL as lname, state, city
from 
    table2
where
    state='toronto'
order by id;

 id |   fname    |   lname    |   state    |    city    
----+------------+------------+------------+------------
  2 | xxxxx2     | xxxxx2     | toronto    | xx2       
  3 | yyyyy3     | (null)     | toronto    | yy3       
  4 | zzzzz3     | zzzzz3     | toronto    | yy3