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!

@Sam Wow I should have know that joins had something to do with this! I should have done more research! Thanks for the link! And I was able to make a query for the example I provided in the question using inner joins: SELECT username FROM players INNER JOIN accounts ON players.id= accounts.account_id INNER JOIN purchases ON accounts.id = purchases.account_id WHERE purchases.price >= 30; — Jon McPherson, Sep 21 '13 at 21:38
Well, everybody started once :). That query itself looks right, however, you do just join *one single* purchase, so you possibly miss purchases with price > 30 (I assume a one to many relationship). A subselect on those purchases would do it, alternatively, you could use right outer joins and distinct, or simply put the `price > 30` condition into the join predicate for purchases — Sam, Sep 21 '13 at 21:49

Franco · Accepted Answer · 2013-09-21T22:18:14.890

8

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.

edited Sep 21 '13 at 22:18

answered Sep 21 '13 at 21:46

Franco

11,845
7
27
33

It's up to you. Basically, it's good to have an actual answer to accept. However, that query does not return a correct result, if a player has multiple purchases or accounts. – Sam Sep 21 '13 at 21:57
3

Lively, as expected from a weasel xD. I'm not here to steal an answer, just try this: `SELECT 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 and pc.price > 30) ;` ... We're talking about money here :) – Sam Sep 21 '13 at 22:06
1

Oh ya that executed quicker than my solution. I wouldn't have thought to exclude the Where and instead filter the results in the second join. I wonder if that is what cause the decrease in execution time, or if it is cause of the aliases. Also are aliases only used to make queries textually shorter? – Jon McPherson Sep 21 '13 at 22:16
1

Yes, in theory (SQL vendor independently) filtering while joining is faster because WHERE clause do the filtering after the joins have been finished and you get the 'temporal' table. Sam's solution is faster because it applies the filters in the joining process. But today sql engines are smart enough to detect this situations and optimizes them. See http://stackoverflow.com/questions/10297231/where-clause-vs-on-when-using-join – Franco Sep 21 '13 at 22:24

SQL select entries in other table linked by foreign keys

1 Answers1