Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
db admins, I require your help!
My experience working with databases directly using SQL is rather limited, since I've mostly been using ORMs to cover all my needs as a frontend developer, but this time I need to use SQL to
look up a token in tokens table that is using an id column of users table as its foreign key in which(inusers table) I only have access to the information stored in username column. I'm using PostgreSQL database.
Here's a visualization:
Appreciate your help
The answer you came up with can work but is liable to error out should your subquery ever produce multiple results (though I'd assume unlikely) and likely is less performant than a more relational approach such as using a JOIN like mustaccio mentioned in the comments. You should definitely read up on them, which would allow you to produce a query like such:
SELECT token
FROM tokens
INNER JOIN users
ON tokens.id_ref = users.id
WHERE users.username = '<username>'
(I leave this answer here just for the context of J.D's solution, use JOINs)
Ok, so after fiddling with it for a bit, I found my solution:
SELECT token FROM tokens WHERE id_ref = (SELECT id FROM users WHERE username = '<username>');