SQL select entries in other table linked by foreign keys

Question

I have redesigned my database structure to use PRIMARY and FOREIGN KEYs to link the entries in my 3 tables together, and I am having problems trying to write queries to select data in one table given data in a another table. Here is an example of my 3 CREATE TABLE statements:

CREATE TABLE IF NOT EXISTS players (
    id INT(10) NOT NULL AUTO_INCREMENT,
    username VARCHAR(16) NOT NULL, 
    uuid VARCHAR(200) NOT NULL DEFAULT 0,
    joined TIMESTAMP DEFAULT 0,
    last_seen TIMESTAMP DEFAULT 0,
    PRIMARY KEY (id)
);

/*      ^
    One |
       To
        | One
        v
*/

CREATE TABLE IF NOT EXISTS accounts (
    id INT(10) NOT NULL AUTO_INCREMENT,
    account_id INT(10) NOT NULL,
    pass_hash VARCHAR(200) NOT NULL, 
    pass_salt VARCHAR(200) NOT NULL, 
    created BIGINT DEFAULT 0,
    last_log_on BIGINT DEFAULT 0,
    PRIMARY KEY (id),
    FOREIGN KEY (account_id) REFERENCES players(id) ON DELETE CASCADE
) ENGINE=InnoDB;

/*      ^
    One |
       To
        | Many
        v
*/

CREATE TABLE IF NOT EXISTS purchases (
    id INT(10) NOT NULL AUTO_INCREMENT,
    account_id INT(10) NOT NULL,
    status VARCHAR(20) NOT NULL, 
    item INT NOT NULL, 
    price DOUBLE DEFAULT 0,
    description VARCHAR(200) NOT NULL,
    buyer_name VARCHAR(200) NOT NULL,
    buyer_email VARCHAR(200) NOT NULL,
    transaction_id VARCHAR(200) NOT NULL,
    payment_type VARCHAR(20) NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (account_id) REFERENCES accounts(account_id) ON DELETE CASCADE
) ENGINE=InnoDB;

Say for example, I want to select all the usernames of users who purchased anything greater than $30. All the usernames are stored in the players table, which is linked to the accounts table and that is linked to the purchases table. Is this this the best way to design this relational database? If so, how would I run queries similar to the above example?

I was able to get get all of a users purchase history given their username, but I did it with 2 sub-queries... Getting that data should be easier than that! Here is the SELECT query I ran to get all of a players purchase data:

SELECT * 
FROM purchases
WHERE account_id = (SELECT id FROM accounts WHERE account_id = (SELECT id FROM players WHERE username = 'username'));

Also, when I try to make references to the other tables using something like 'players.username', I get an error saying that the column doesn't exist...

I appreciate any help! Thanks!

Answer 1

Your design is ok in my opinion. The relation between players and account is one-to-many and not one-to-one since this way, you can have two tuples referencing a single player.

I would write the query you need as:

SELECT DISTINCT p.id, p.username
FROM players p INNER JOIN accounts a ON (p.id = a.account_id)
               INNER JOIN purchases pc ON (a.id = pc.account_id)
WHERE (pc.price > 30);

As Sam suggested, I added DISTINCT to avoid repeating id and username in case a user have multiple purchases. Note the id is here to avoid confusion among repeated usernames.