php - 用于搜索器的两个非关系表的 SQL 语句

Question

我正在为我的网页做一个搜索器，我遇到了一个小问题，我想从两个非关系表中获取信息，但数据没有按照我想要的方式返回

表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

表2

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

目前我有

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

这会显示这样的结果

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

但我想要这样

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

编辑:

使用 UNION asnwer，我得到了具有我想要的格式的数据，但当我搜索公共(public)值时仅显示一个表的结果，例如:

如果我输入“Toronto”，则必须显示

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

但仅显示此

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

Answer 1

我猜您想连接这两个表。然后...

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>

编辑

如果你有:

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  

然后:

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