Search names in 2 different tables

0

I want to search 2 database tables where the name is equal to $nome variable.

I would not like to gather data as if it were FK, but to pull all the information from these tables (other columns).

administradores
adm_id   | nome    | data_nasc  | cod_user |
1        | rafael  | 00/00/0000 | 63453    |
2        | paulo   | 05/06/2005 | 34241    |

usuarios
user_id  | nome     | sobrenome | cod_user |
1        | rogério  | silva     | 32412    |
2        | silvio   | lira      | 21321    |

I tried to make a select like this:

SELECT * FROM administradores.nome, usuarios.nome WHERE nome = '$nome';
    
asked by anonymous 21.11.2018 / 14:28

4 answers

2

You can use UNION, however, you would need to have the same column names to include in both "SELECT", however, you can do a small "adjustment" allowing you to know the result if you are a user or administrator:

SELECT user_id, 0 as adm_id, nome FROM usuarios where nome like '$nome'
UNION ALL
SELECT 0 as user_id, adm_id, nome FROM administradores where nome like '$nome';

In this way the two "SELECT" will be filtered before the union and its result will have the columns "user_id", "adm_id" and "name".

user_id  | adm_id   | nome
1        | 0        | rogério
2        | 0        | silvio
0        | 1        | rafael
0        | 2        | paulo
    
21.11.2018 / 14:44
0

By what I understood from your question and taking into consideration the syntax you have already used, to fetch everything from the 2 tables that have the same value as the $ name variable, you can do:

select * from administradores a, usuarios u where a.nome = '$nome' and u.nome = '$nome'
    
21.11.2018 / 14:38
0

Good morning. I would do the following:

SELECT adm_id, nome FROM administradores
WHERE nome = '$nome'
union
SELECT user_id,nome FROM usuarios WHERE nome = '$nome'

I believe it will work in any database

    
21.11.2018 / 14:38
0

You will need to use UNION :

SELECT adm_id AS id, nome 
FROM administradores
WHERE nome = 'nome_pesquisado'
UNION 
SELECT user_id AS id, nome
FROM usuarios
WHERE nome = 'nome_pesquisado'

Thus, the names of the users / admins searched for and their ids (in each table) will be returned.

    
21.11.2018 / 14:39