Discussion :

[Cyril_du_13]

Bonsoir,
Développeur confirmé Java, je débute en SQLite et je recherche depuis plusieurs heures maintenant à créer une requête que je pensai être simple

J'ai 2 tables :
une table A avec un id et un nom
une table B avec un id et un aid

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
table A
id                 nom
-----              --------
1                   n1
2                   n2
3                   n3
 
table B
id                 aid
-----              --------
1                   1
2                   1  
3                   3

la but de ma requête est de me donner le nombre d'insertion dans la table B de chaque id de la table B, ma requête devrait me sortir les résultats suivants:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
id                 nom                nombre
-----            --------          -------
1                   n1                2
2                   n2                0
3                   n3                1

Qui pourrait m'aider sur le sujet? merci

[punkoff]

Bonjour,

lisez ceci :
http://sqlpro.developpez.com/cours/sqlaz/ensembles/
La partie sur les group by

Et ceci :
http://sqlpro.developpez.com/cours/sqlaz/jointures/
La partie sur les jointures externes

Et si vous n'y arrivez pas avec ca, revenez avec vos essais

[seabs]

Bonjour,

Sqlite est une base de données type Access, c'est-à-dire fichier à plat ayant des possibilités de SQL.

Les nouvelles versions après 3.6.3, je pense, assurent la gestion des clés étrangères.

Sans être un spécialiste de cette base, une seule application significative, j'ai quelques notions qui vont pouvoir vous aider.

Je ne vais pas répondre directement, mais j'ai construit un exemple en rapport avec votre demande. Sa lecture doit pouvoir vous aider.

J'ai créé vos deux tables, mais en ajoutant une clé étrangère dans la table B. Nous avons :

Table A(IdA, Nom)
Table B(IdB, Aid, #IdA)
IDA dans la table B est la clé étrangère qui reprend l'index de la table A.

En reprenant vos données, j'ai établi la requête ci-dessous.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT A.IDA, Nom, COUNT(Aid)
FROM A
  LEFT JOIN B ON B.IdA = A.IDA    
GROUP BY A.IDA, Nom

Le resultat

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
IdA	Nom	COUNT(Aid)
1	N1	2
2	N2	0
3	N3	1

Pour vous permettre de reproduire l'exemple, je vous joins le code pour créer les deux tables.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
CREATE TABLE [A] (
  [IdA] INTEGER NOT NULL ON CONFLICT ROLLBACK PRIMARY KEY ON CONFLICT ROLLBACK, 
  [Nom] NVARCHAR);

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
CREATE TABLE [B] (
  [IdB] INTEGER NOT NULL PRIMARY KEY ON CONFLICT ROLLBACK, 
  [Aid] INTEGER, 
  [IdA] INTEGER NOT NULL CONSTRAINT [FK_1] REFERENCES [A]([IdA]) ON DELETE NO ACTION ON UPDATE NO ACTION);

Il vous faut savoir que les instructions SQL ne semblent pas respecter rigoureusement la norme SQL92, sous réserve d'avis plus avertis que le mien sur ce sujet.

Sans l'utilisation d'une clé étrangère, le décompte doit être possible avec une union des tables A et B. Je n'ai pas fait l'essai.

Bon courage et A+

[CinePhil]

Bel effort seabs mais il me semble que dans la structure donnée par Cyril_du_13, B.aid fait justement référence à l'identifiant de la table A !

[Cyril_du_13]

Bonjour a tous,

lisez ceci

Je fréquente très peu les forum car j'essaye toujours au plus d'être autodidacte et d'apprendre par moi même j'avais donc bien déjà lu et relu ce tutorial.
J'avais d'ailleurs réussi à avoir tout ce que je voulais mais jamais en une seule requête.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT COUNT(*) AS nombre,id FROM B GROUP BY aid

et

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT id, nom FROM A

pour le moment j'ai essayé plusieurs choses comme:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
SELECT A.id, A.nom, COUNT(B.id) AS nombre
   FROM A,B
   GROUP BY A.id
 
id                 nom                nombre
-----            --------          -------
1                   n1                3
2                   n2                3
3                   n3                3

et ca:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT id, nom, nombre
   FROM A, (
      SELECT COUNT(*) AS nombre, aid 
         FROM B
         GROUP BY aid
   )
 
id                 nom                nombre
-----            --------          -------
1                   n1                2
2                   n2                2
3                   n3                2
1                   n1                0
2                   n2                0
3                   n3                0
1                   n1                1
2                   n2                1
3                   n3                1

Je sais que SQLite peut être très différent de SQL donc j'essayai d'éviter les jointures. L'exemple qu'à fournit seabs (merci à lui) par exemple ne fonctionne pas. et renvoi 0 partout dans la colonne count...

Je voulais savoir quelle est la méthode classique pour faire cela en SQL que je teste si elle marche en SQLite. Ca me dérangerai de devoir faire une requête en plus par A.id dans mon application pour savoir son nombre occurrence dans la table B

FYI, voici mes créations de table si cela peut vous aider:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE A (
   id INTEGER PRIMARY KEY NOT NULL,
   nom TEXT NOT NULL
);
 
CREATE TABLE B (
   id INTEGER NOT NULL,
   aid INTEGER NOT NULL,
      FOREIGN KEY(aid)
      REFERENCES A(id)
);

[punkoff]

vous auriez présentez la chose comme ca j'aurai développé beaucoup plus.

Votre 2eme exemple avec une légère modification, ne fonctionne-t-il pas ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
SELECT id, nom, (
      SELECT COUNT(*) AS nombre, aid 
         FROM B
         WHERE A.Id = B.aid
   )
FROM A

(le group by devient inutile dans notre cas mais peut être est-il nécessaire pour SQLite)


sinon il y a un sous-forum SQLite il me semble ici :
http://www.developpez.net/forums/f15...s-sgbd/sqlite/

[Cyril_du_13]

merci cela fonctionne comme cela le devrait, il s'agissait donc bien de requêtes imbriquées qu'il fallait utiliser mais l'imbrication était mal située et je comprend mieux d'ailleurs le fonctionnement que j'obtenais du coup.

Merci encore de votre aide.

Cyril

[seabs]

Bonjour,

J'ai modifié mes tables pour les accorder avec les tiennes. Ma requête devient :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT A.IDA, Nom, COUNT(Aid)
FROM A
  LEFT JOIN B ON B.Aid = A.IDA    
GROUP BY A.IDA, Nom

et le résultat reste le même

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
IdA	Nom	COUNT(Aid)
1	N1	2
2	N2	0
3	N3	1

Si cela fonctionne chez moi, il n'y a aucune raison d'avoir des résultats erronés chez toi.

J'ai fais mes essais avec Sqlite 3.7.6.2 et utilisé l'administrateur de bases Sqlite Expert version 3.3.22 Personal Edition qui est freeware.

Pour les jointures, elles fonctionnent parfaitement avec SQlite. J'ai d'ailleurs développé une application de facturation avec bons de commande, lignes de produits, tarifs avec modifications régulières, etc. Sans utilisation des jointures, il m'aurait été impossible de conduire à son terme ce développement. Il est utilisé depuis plus de 2 ans en permanence, nous n'avons aucune anomalie.

En fait, Sqlite accepte parfaitement les clés étrangères, ses faiblesses sont plutôt dans le contrôle de l'intégrité référentielle.

Tu pourrais montrer le code ma requête chez toi, afin de voir pourquoi, elle retourne zéro dans la colonne Count.

Edit - Complément

Un essai avec la requête de @punkoff

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 SELECT idA, nom, 
  (SELECT COUNT(*) AS nombre FROM B WHERE B.Aid = A.IdA)
FROM A

Nous donne des résultats exacts

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
IdA	Nom	(SELECT COUNT(*) AS nombre FROM B WHERE B.Aid = A.IdA)
1	N1	2
2	N2	0
3	N3	1

Edité le 19/07/2011 - Correction de quelques fautes

A+

[Cyril_du_13]

En effet seabs ta requête marche bien , la fois d'avant il y avait du avoir une confusion entre id et aid...
et d'ailleurs je suis très dégouté car j'avais dejà fait cette requête lors de mes essais et je ne l'avais pas prise car au lieu de mettre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
SELECT A.id, A.nom, COUNT(B.aid) AS nombre
FROM A
  LEFT JOIN B ON B.aid = A.id    
  GROUP BY A.id
 
id                 nom                nombre
-----            --------          -------
1                   n1                2
2                   n2                0
3                   n3                1

J'avais mis :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
SELECT A.id, A.nom, COUNT(B.aid) AS nombre
FROM A
  INNER JOIN B ON B.aid = A.id    
  GROUP BY A.id
 
id                 nom                nombre
-----            --------          -------
1                   n1                2
3                   n3                1

du coup j'avais écarté ce type de requêtes pensant que je n'aurai que les ids "communs" de A dans B...
comme quoi les jointures ne sont vraiment pas comprises du tout dans mon cas