How do I check if a user has a confirmed email with only one query?

Question

I have 2 tables, one of users and one of confirmed emails, which has a user FK and a confirmed user bool:

- tbl_valida_email
=========================
fk_id_usuario
usuario_confirmado

I was using the following query for this:

SELECT * FROM tbl_usuario WHERE email='email' AND senha='senha' AND (SELECT valido FROM tbl_valida_email WHERE id_usuario=(SELECT id FROM tbl_usuario WHERE email='email' AND senha='senha'));

And in my opinion, it’s too complex for one simple thing. How to improve it?

Answer 1

You can link the two tables with the clause JOIN. Using INNER JOIN you will only result if there are records in the two tables.

SELECT *
  FROM tbl_usuario tu
       INNER JOIN tbl_valida_email tve ON tve.id_usuario = tu.id
 WHERE tu.email='email'
   AND tu.senha='senha'
   AND valido = TRUE;