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

Question

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

Navigation

#1 by (4 votes)

2

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 a simple thing. How do you improve it?

mysql

asked by anonymous 15.09.2017 / 02:40

1 answer

How to expand range of numbers with data? How do I start jquery results in a div?

score 4 · Accepted Answer

You can link the two tables with the JOIN clause. Using INNER JOIN you only get results if there are records in both 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;