How to make SELECT in more than one bank table?

Asked

Viewed 671 times

3

I have two tables in the database: zip code and users. Can I do a single query to get fields from both tables? Both have idCep. I want to get the address data of the cep table and the name and user of the table users.

As I am working with php, I am using PDO with the database.

Follows print of the tables: inserir a descrição da imagem aqui inserir a descrição da imagem aqui

php mysql sql pdo

3 answers

7


That would be the basics:

SELECT *
  FROM USUARIOS
  JOIN CEP ON CEP.IDCEP = USUARIOS.IDCEP

If you want to limit the columns:

SELECT USUARIOS.*, CEP.cep, CEP.uf, CEP.cidade, CEP.bairro, CEP.logradouro
  FROM USUARIOS 
  JOIN CEP ON CEP.IDCEP = USUARIOS.IDCEP

Consideration:

The CEP number (cep column) is already the key of the table. So you should not have an Idcep, first for not making sense, second for losing normality.

  • For losing normality?

  • I forgot to mention, but I am using PDO. $dadsUsuario=$Pdo->prepare("SELECT * FROM usuarios JOIN cep ON WHERE cep.idCep=:usuarios.idCep");

  • Yes, @Gustavosevero, you now have two keys, making it possible to have repeated Zip Codes in the table. See here about Data Normalization.

  • 1

    @Gustavosevero: Fix this one ON WHERE, follow: $dadosUsuario=$pdo->prepare("SELECT * FROM usuarios JOIN cep ON cep.idCep=:usuarios.idCep");

  • OK, it worked. Just the following, more than one result appeared for this query. I can’t say that both have to be equal to such id? Because ALL the idCep of the cep table have the same idCep for the users table, so will appear numerous results.

  • @Gustavosevero ERRATA: $dadosUsuario=$pdo->prepare("SELECT * FROM usuarios JOIN cep ON cep.idCep = usuarios.idCep AND cep.idCep=:usuarios.idCep");

  • Nothing changed in the results kkk Several appear. I want to do something like: SELECT * FROM usuarios JOIN cep ON cep.idCep = usuarios.idCep WHERE idCep=:idCEP

Show 2 more comments

3

Select CEP.logradouro,
       CEP.bairro,
       CEP.cidade,
       CEP.uf,
       Usuarios.nome,
       Usuarios.usuario
  From CEP
  Inner Joint Usuarios
    on CEP.idCep = Usuarios.idCep

0

Just do join between the two tables

select *
    from Usuario, Cep
    where Usuario.idCep = Cep.idCep

Browser other questions tagged php mysql sql pdo

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