Find names in 2 different tables

Asked

Viewed 79 times

0

I want to search in 2 tables of the database where the name equals the variable $nome.

I would not like to join data as if they were FK, but 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 thus:

SELECT * FROM administradores.nome, usuarios.nome WHERE nome = '$nome';
php mysql sql

  • And what is the expected result?

  • Search tables for the name

  • to bring data from two tables you need to make a join. What do you expect to receive with this select? Bring the name of the two tables?

  • Could you give examples based on the data you put in the tables? If I looked for "paul", what would be the result? And if "paul" was in both tables?

  • select * from tabela1 where campo = campo UNION select tabela2 where campo = campo

  • @Teresasantos, in this case, you can use one join or the union as quoted in some responses .

  • That sounds like a XY problem and just with the details presented in the question there is no way to say whether it makes sense or not to do what you’re doing. If they are unrelated tables, I see no reason to create the relationship in the query.

  • I just want to create a search system where I can search in a single input names that are in 2 separate tables

  • 1

    Then improve your question by adding concrete examples. Place the complete structures of the two tables, some sample data and the expected results for some hypothetical queries. Without this, there is no way to verify the validity of the solution and any response will be based on speculation.

Show 4 more comments

4 answers

2


You can use "UNION" however, you would need to have the same column names to be included in both "SELECT", however, you can make a small "adjustment" allowing knowing 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';

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

  • Problem may arise when both tables have the same name. Two results will be generated. It gets worse if the table columns are not equal and it gets worse if the two people are not the same person. If there is a Fulano administrator and a Fulano user, how to know which data to use?

  • This way that I put it can distinguish administrator user by the id corresponding to each table... as they are different tables I tried a way to use the columns of the other table by assigning the id "0" (zero)

  • I mean "look for Paul’s birth date". If there is "paul" in both tables and they are different people, from whom do I get the date of birth? The administrator or the user? And if one of the tables does not have the column date of birth?

  • Well then it will depend on how the colleague who asked to assemble their tables, this will be a case to him consider.

0

From what I understood of your question and taking into account the syntax you have already used, to search all of the 2 tables that have the same value of the variable $name, you can do:

select * from administradores a, usuarios u where a.nome = '$nome' and u.nome = '$nome'

0

Good Morning. Here’s what I’d do:

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

  • What about tables having columns with different names? If I want to check the result id I should look at the value of adm_id or user_id?

  • The Output will always take the name of the fields of the first select. Obligatorily, you need to name them if they are calculated fields, for example: SELECT VALOR_1 + VALOR_2 AS TOTAL....

0

You will need to use the 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/administrators researched and their ids (in each table).

Browser other questions tagged php mysql sql

You are not signed in. Login or sign up in order to post.