Entrées de sélection SQL dans une autre table liée par des clés étrangères

J'ai repensé à ma structure de base de données pour utiliser les Clés PRIMAIRES et ÉTRANGÈRES pour relier les entrées dans mes 3 tables, et je vais avoir des problèmes en essayant d'écrire des requêtes pour sélectionner des données dans une table de données à une autre table. Voici un exemple de mes 3 instructions CREATE TABLE:

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=MyISAM;

/*      ^
    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=MyISAM;

Dire par exemple, je veux sélectionner tous les noms des utilisateurs qui ont acheté quelque chose de plus grand que $30. Tous les noms d'utilisateurs sont stockés dans les joueurs de la table, qui est liée à la comptabilité de la table et qui est liée à la table des achats. Est-ce ce la meilleure manière de la conception de cette base de données relationnelle? Si oui, comment pourrais-je exécuter des requêtes similaires à l'exemple ci-dessus?

J'ai été en mesure d'obtenir la totalité d'un des utilisateurs de l'historique d'achat compte tenu de leur nom d'utilisateur, mais je l'ai fait avec 2 sous-requêtes... Arriver que les données devraient être plus facile que ça!
Voici la requête SELECT, j'ai couru pour obtenir tout d'un achat de joueurs de données:

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

Aussi, lorsque je tente de faire des références à d'autres tables à l'aide de quelque chose comme " les joueurs.nom d'utilisateur', j'obtiens un message d'erreur indiquant que la colonne n'existe pas...

J'apprécie toute l'aide! Merci!

source d'informationauteur Jon McPherson

  1. 5

    Votre design est ok à mon avis. La relation entre les joueurs et le compte est un-à-plusieurs et pas un-à-un, car de cette façon, vous pouvez avoir deux n-uplets de référencement d'un seul joueur.

    Je voudrais écrire la requête que vous avez besoin comme:

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

    Que Sam a suggéré, j'ai ajouté DISTINCTS pour éviter de répéter l'id et le nom d'utilisateur dans le cas où un utilisateur achats multiples.
    Notez l'id est ici d'éviter la confusion entre répété les noms d'utilisateur.