Sub Query with AS Keyword Help

[Link]

Hi all!

 

I always confuse myself with subqueries and the AS keyword.  When I run the follow query, I get a uid does not exist (even when I wrap it with `uid`).  ERROR: #1054 - Unknown column 'uid' in 'where clause'

 

SELECT `rmnd_main`.`users`.*, 
`rmnd_main`.`users`.`id` AS uid 
FROM `rmnd_main`.`users` 
WHERE `rmnd_main`.`users`.`password` = 'PASS' 
AND 
  ( 
    (SELECT COUNT(*) FROM `rmnd_main`.`phones` WHERE `user_id` = uid AND `number` = 1234567890) = 1 
    OR 
    (SELECT COUNT(*) FROM `rmnd_main`.`emails` WHERE `user_id` = uid AND `email` = 1234567890) = 1 
  )

Basically have three tables and wait to see if this value (which is an email address or a phone) is in either the phones or emails table and that the password matches in the users table.  I can always just check the variable to see if it is an integer or contains an @ and make assumptions based on that, but I love learning how to properly write these queries.

 

Let me know, and thanks in advance!

 

[fenway]

That's because those sub-queries are being run "first", and aren't correlated.

 

What you actually want to use is "EXISTS".

[Link]

Can you rewrite it with the EXISTS so that I can see the format?

[fenway]

SELECT `rmnd_main`.`users`.*,
`rmnd_main`.`users`.`id` AS uid
FROM `rmnd_main`.`users`
WHERE `rmnd_main`.`users`.`password` = 'PASS'
AND
  ( EXISTS 
    (SELECT * FROM `rmnd_main`.`phones` WHERE `user_id` = uid AND `number` = 1234567890)
      OR
   EXISTS (SELECT * FROM `rmnd_main`.`emails` WHERE `user_id` = uid AND `email` = 1234567890) 
)
  

Of course, you shouldn't be sending passwords in plain-text across the network.