How to query through foreign keys while only having the value in the the same row as the value used as foreign key in another column

All we need is an easy explanation of the problem, so here it is.

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:
How to query through foreign keys while only having the value in the the same row as the value used as foreign key in another column

Appreciate your help

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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>'

Method 2

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>');

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply