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
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