Postgres query from two different table

Question

Hi i have queries that I use to get single result. How could I do this with just one query?

First I ask

SELECT "userId" FROM "Devices" WHERE "deviceNumber" = '0001563055080020'

Then save UserId and ask following another table

SELECT "email" FROM "Users" WHERE "id" = '1'

Any ideas?

Answer 1

NESTED QUERY

SELECT "email" 
FROM "Users" 
WHERE "id" IN (
    SELECT "userId" 
    FROM "Devices" 
    WHERE "deviceNumber" = '0001563055080020')

INNER JOIN

select u.email
from users u, devices d
where d.userid = u.id
and d.deviceNumber = '0001563055080020'

Answer 2

this is called a join

here is one syntax

select email
from users u, devices d
where d.userid = u.id

Answer 3

I recommend using the new join syntax. I think it's cleaner and easier to read. One can immediatly see there's an inner join. Furthermore the where-clause can be used just for filtering.

select u.*
from users u
inner join devices d on u.id=d.user_id
where d.device_number='0001234';