Problème De Base De Données

Désolé pour le titre assez peu clair, je n'ai pas trouvé mieux...

Mes connaissances en SQL sont assez faibles et là je bute un peu. Mon problème est le suivant :

Je cherche à faire l'équivalent d'un arbre technologique (style Civilization, si ça peut aider à comprendre) :
Il y a différents "Savoirs" qui sont accessibles aux joueurs.
Chaque savoir peut demander un ou des pré-requis (il est indispensable d'avoir la navigation et le travail du fer ainsi que la machine à vapeur pour inventer le bateau à vapeur, par exemple, mais l'agriculture n'a aucun pré-requis). Ce nombre de pré-requis est variable, et j'aimerais qu'il n'ait pas de limite.

J'ai organisé ça de la façon suivante :
Une table de savoirs, qui décrit ce qu'apporte cette découverte
Une table de pré-requis, avec comme champs l'id du savoir concerné et l'id du savoir pré-requis
Une table de connaissances, avec comme champs l'id du joueur (qui est dans une autre table) et l'id du savoir possédé.

Jusque là ça me semblait bien parti, mais je veux maintenant être capable d'obtenir tous les savoirs accessibles à un joueur (il faut donc qu'il remplisse tous les pré-requis). Et là je coince, j'obtiens facilement tous les pré-requis d'un savoir ou tous les pré-requis qu'un joueur possède, mais je n'arrive pas à savoir si le Joueur possède tous les pré-requis d'un savoir.

Je suppose que c'est un problème classique que tous les connaisseurs ont déjà résolu, mais là ça me dépasse complètement. J'ai tenté pas mal de choses (avec des sous-requêtes sur des vues et des horreurs du genre), sans résultat.

Je suis preneur de tous vos avis et conseils.
Merci d'avoir lu !

Je dirais :

select savoir.*
from savoir, prerequis, connaissance
where savoir.savoir_id = prerequis.savoir_id
and prerequis.savoir_prerequis_id = connaissance.savoir_id
and connaissance.joueur_id = XXX

Si je ne me trompe pas, ça ne donnerait que les savoirs qui ont un prérequis, et ils apparaitraient même si un seul des prérequis est rempli.

Le premier problème serait résolu par un LEFT OUTER JOIN, mais le second me gêne davantage...

Exact, je croyais que tu faisais un arbre...

Ah c'est vrai que le terme "d'arbre technologique" reflète pas exactement ce que je veux... Ou alors faudrait trouver un arbre dont les branches peuvent fusionner parfois... Merci quand même de ton aide

Ca devrait marcher, remplace juste research_template par savoir, research_link par prerequis et researches par savoir

j'essaie d'assimiler ça

Les noms que j'avais donné plus haut sont pas les vrais, c'était pour me faire comprendre facilement. Je reviendrais dire ce que ça donne, mais en tout cas merci beaucoup de t'être donné la peine de chercher ça.

EDIT : Ca y est je commence à comprendre Je savais pas qu'on pouvait accéder dans une sous requête à un champ sélectionné dans la requête initiale !

J'ai un message d'erreur comme quoi l1.child_id est une colonne qui n'existe pas... Peut-être que c'est possible d'accéder à des champs de ta requête principale depuis une sous-requête en MySQL mais pas en SQLite ?
(je fais ça en SQLite, et pas du tout la dernière version en plus...)

Salut,

Peux-tu nous filer la structure de tes tables et les 2 requêtes que tu utilises ?
Ca aiderait

Bon, l'exemple de l'arbre technologique est plus simple que le truc que j'utilise, donc on va rester dans cette voie là :

CREATE TABLE Technologies (Id INTEGER PRIMARY KEY, Nom VARCHAR (30));
CREATE TABLE Prerequis (Id INTEGER PRIMARY KEY, Id_Tech, Id_Requis);
CREATE TABLE Connaissances (Id INTEGER PRIMARY KEY, Id_Joueur, Id_tech);

EDIT :

Et voilà la requête fournie plus haut par the-gtm selon ces structures :

SELECT Technologies.*
FROM Technologies, Prerequis
where Technologies.id = Prerequis.Id_Tech
group by Technologies.id
having count(Prerequis.Id_Requis) = (
select count(*)
from Prerequis P2, Connaissances
where Connaissances.Id_Tech = P2.Id_Requis
and P2.Id_Tech= Prerequis.Id_Tech
and Connaissances.Id_Joueur = 1
group by P2.Id_Tech)

Moi ça me donne :
Erreur : SQL logic error or missing database
no such column: Prerequis.Id_Tech

Merci de ton attention

Si c'est bien un problème venant de SQLite et pas de ma syntaxe je vais peut-être pouvoir m'en sortir avec une vue. Ca dépasse très largement ce que j'ai à faire en SQL d'habitude, alors je suis un peu perdu, mais au moins vous m'avez donné matière à travailler, merci

J'ai testé avec MySQL, peut être qu'effectivement ce n'est pas supporté par SQLite

Je viens de tester ce que je disais plus haut avec MySQL et j'ai la même erreur, c'est donc moi qui me plante, plutôt qu'une limitation de SQLite, et à vrai dire, je préfère largement ça !

EDIT: the-gtm je suppose que tu as trouvé quelquepar la requête que tu m'as fournie plus haut, tu peux me dire où ? C'est assez frustrant d'être aussi près du but et de ne pas y arriver

J'ai fait la requête moi même et elle marche sur MySQL 5

Je sais que le Sql est partiellement sensible à la casse mais je ne sais plus pour quoi il l'est et pour quoi il ne l'est pas...

or ici ...

CREATE TABLE Connaissances (Id INTEGER PRIMARY KEY, Id_Joueur, Id_tech);
donc à mon avis ca devrait être :

where Connaissances.Id_tech = P2.Id_Requis

Voilà à verifier

La doc mysql nous dit :

C'est souvent cause d'erreur car les gens développent dans leur coin sous Windows sans se préoccuper de la casse et une fois en ligne, le site est généralement hébergé sur un serveur Linux et sa merde sans qu'on comprenne pourquoi.

C'est vrai que ça aurait pu être ça. En fait j'ai retappé ici les CREATE TABLE sans faire vraiment attention, et dans ma bdd l'erreur n'y est pas.

Le problème semble vraiment venir du fait que j'accède dans la sous-requête à une valeur de la requête principale, et pourtant une requête un peu bête comme :
SELECT *
FROM prerequis AS P1
WHERE P1.Id_Tech
IN (

SELECT P2.Id_Tech
FROM prerequis AS P2
WHERE P1.Id <> P2.Id
)

passe sans problème...

En tout cas merci de votre aide
EDIT : Ah non, même ça, ça passe pas sous SQLite on dirait... , alors qu'en MySQL c'est bon

SQLite ne gère pas les requêtes imbriquées.

Ok, merci ! j'y vois un peu plus clair comme ça

Je pense être sur une bonne voie, là.

C'est peut-être mon anglais qui me joue des tours, mais cette partie de la doc me laissait croire que c'était possible :
"SELECT statements can appear in expressions as either the right-hand operand of the IN operator, as a scalar quantity, or as the operand of an EXISTS operator. As a scalar quantity or the operand of an IN operator, the SELECT should have only a single column in its result. Compound SELECTs (connected with keywords like UNION or EXCEPT) are allowed. With the EXISTS operator, the columns in the result set of the SELECT are ignored and the expression returns TRUE if one or more rows exist and FALSE if the result set is empty. If no terms in the SELECT expression refer to value in the containing query, then the expression is evaluated once prior to any other processing and the result is reused as necessary. If the SELECT expression does contain variables from the outer query, then the SELECT is reevaluated every time it is needed."

merci pour la précision Naholyr

Voilà, j'ai fini par y arriver, merci à tous de votre aide et de vos conseils !

Voilà ce que j'ai fini par faire, c'est lent (4.5 ms sur mon p'tit portable), mais ce sera utilisé assez rarement de toutes façons :

Ca donne toutes les technologies qui ne demandent pas de pré-requis, ou pour lesquelles on a rempli tous les pré-requis. Faut noter que ça donne aussi les technologies qu'on possède déjà. Ca serait facilement correctible mais ça ne me pose pas pbme.

EDIT : pour être plus clair RPT veut dire Requis Par Techno et RPJ Requis Par Joueur

Attention quand même à la façon dont ça augmente avec le nombre de savoirs/prérequis/joueurs !

Ouais, je vais me méfier, et lancer des tests dès aujourd'hui. Je verrai alors aussi comment ça évolue en plaçant mes indexes.

D'ailleurs vous avez des conseils pour ces index (à part essayer, évidemment) ?

Déjà mettre les clés étrangères c'est un bon début, dans ton cas ça devrait d'ailleurs suffire.

Sinon ce que je fais c'est afficher le plan des requêtes, si la base fait un full scan d'une table en général ça veut dire qu'il manque un index.