Re: SQL help?

Trying to do something very easy, but can't quite figure out the logic.
Typing out the email, and see if I can't figure it out just typing it
out.

Have two tables:

petitions
id = (int)primary key
name = text description

and the following

signed_petitions
signed_id = references petitions.id (Foreign key)
joined = boolean

my goal is to list those petitions that have the most signatures, so I
have to get a count of joined=true, and list the names.

What confuses me on a fundamental level, is the usage of the Foreign
key, reference. But, here is my best attempt.

Ok, this is a lot harder than I thought, because the boolean is not
actually a number. So I guess I will have to use a rank?

SELECT
    petitions.id
    petitions.name
    RANK() OVER (PARTITION BY signed_petitions.joined) AS total_signed
WHERE
    signed_petitions.joined = "TRUE"
ORDER BY total_signed DESC
LIMIT 10