SQL Select based on Link Field

Question

I feel like an idiot asking this...

Table 1: users
id serial
person integer
username char(32)

Table 2:persons
id serial
name char(16)

Can I run a query that returns the name field in persons by providing the username in users?

users
1 | 1 | larry123

persons
1 | larry
2 | curly

SQL?

select name from persons where users.person=persons.id and users.username='larry123';

with the desired return of

larry

I have been doing it with two passes until now and think maybe a nested select using a join is what I need

1 | larry

Answer 1

It sounds like you're asking how to do a join in SQL:

SELECT
    name
  FROM
    users JOIN persons ON (users.person = persons.id)
  WHERE
    users.username = 'larry123';

that is almost the query you wrote. All you were missing was the join clause. You could also do that join like this:

SELECT name
FROM users, persons
WHERE
      users.person = persons.id
  AND users.username = 'larry123';

I suggest finding a well-written introduction to SQL.