LEFT JOIN et champs nuls

LEFT JOIN et champs nuls - SQL/NoSQL - Programmation

Marsh Posté le 30-05-2007 à 11:33:48    

Yo

 

Voila, je fais un requete du style

 
Code :
  1. SELECT user.user_id, meuble.meuble_id, pays.pays_id
  2. FROM user
  3. LEFT JOIN pays USING pays_id
  4. LEFT JOIN meuble USING user_id
 

Et je voudrais retourner pour chaque pays le nombre d'user ET le nombre d'user ayant au moins un meuble
Ce tableau me retournant (a priori ?) une valeur NULL pour meuble_id si l'utilisateur n'a pas de meubles, je me dis qu'il suffit de soustraire le nombre de (meuble_id=NULL) du nb d'users

 


donc pour l'instant pour ce truc j'ai :

 
Code :
  1. SELECT
  2. pays.pays_id,
  3. COUNT(DISTINCT user.user_id) AS nb_users,
  4. SUM(CASE meuble_id WHEN NULL THEN 1 ELSE 0) AS nb_nulls,
  5. COUNT(DISTINCT user.user_id) - SUM(CASE meuble_id WHEN NULL THEN 1 ELSE 0) AS nb_users_buying,
  6. COUNT(DISTINCT meuble.meuble_id) AS nb_meubles
  7. FROM user
  8. LEFT JOIN pays USING pays_id
  9. LEFT JOIN meuble USING user_id
  10. GROUP BY pays
 

Seulement voila, 'nb_nulls' retourne toujours 0 [:totoz]
C'est comme si il ne trouvait aucun meuble_id avec une valeur NULL, alors qu'en virant le GROUP BY, ben j'en ai...

 


edit : c'était pas clair, alors j'ai encore compliqué le truc :o


Message édité par theredled le 30-05-2007 à 11:39:53

---------------
Contes de fées en yaourt --- --- zed, souviens-toi de ma dernière lettre. --- Rate ta musique
Reply

Marsh Posté le 30-05-2007 à 11:33:48   

Reply

Marsh Posté le 30-05-2007 à 11:42:46    

c'est nawak ta requête :o

 

tu groupes sur un champ (j'imagine user_id) qu'est pas dans ceux remontés par ton select alors qu'en plus tu fais un compte dessus.

 

Je suis pas sur de bien comprendre ce que tu cherches à faire...
Tu souhaites avoir le nombre de users, le nombre de meubles n'appartenant pas à un user, le nombre de meubles apparteant a des users ( en partant des deux précédents résultats) et enfin le nombre de meubles total.
Et tout ça regroupé par ???

 

Mais bon à mon avis t'es mal embarqué :o

 

edit: zouper t'as tout changé [:dawak]


Message édité par anapajari le 30-05-2007 à 11:43:29
Reply

Marsh Posté le 30-05-2007 à 11:44:10    

Ouais je sais, en vrai c'est pas ça la vraie requete et je crois que j'ai mal métaphorisé, je corrige :o

 

edit: voila :o


Message édité par theredled le 30-05-2007 à 11:44:26

---------------
Contes de fées en yaourt --- --- zed, souviens-toi de ma dernière lettre. --- Rate ta musique
Reply

Marsh Posté le 30-05-2007 à 11:44:41    

tu veux pas soit:
- filer la structure des tables
- ecrire tes conditions de jointures explicitement
Histoire qu'on sache quel champ est dans quel table!

Reply

Marsh Posté le 30-05-2007 à 11:54:37    

Yep
 


---- table 'pays' ---
-pays_id-----pays_nom---
1            cuba
2            suisse
3            france
 
---- table 'user' ---
-user_id-----user_nom---pays_id
1          Pouet Dupont    2
2          JP raffarin     3
3          Fidel Castro    1
 
---- table 'meuble' ---
-meuble_id----user_id----prix_paye
1                1           10
2                2           99.9
3                2           49
4                1           29.9
5                1           29


 
Donc tous les meubles ont un user correspondant.
Mais certain users (Fidel Castro) n'ont pas de meubles.
 
Je veux compter  
- le nb d'users total par pays
- le nb d'users total par pays ayant au moins un meuble


---------------
Contes de fées en yaourt --- --- zed, souviens-toi de ma dernière lettre. --- Rate ta musique
Reply

Marsh Posté le 30-05-2007 à 13:19:02    

je ferais un truc dans le genre:

select
 pays_id,
 count(user_id) as nb_users,
 sum(case when user_has_meubles >0 then 1 else 0 end) as nb_users_with_meuble
from  
   pays p
left outer join (
  select  
    u.pays_id as pays_id
    u.user_id as user_id,
    count(*) as nb_meubles
  from
    user u
    left outer join meuble m on u.user_id = m.user_id
  group by
    u.pays_id,
    u.user_id
) t on t.pays_id = p.pays_id

Reply

Marsh Posté le 30-05-2007 à 13:27:54    

c'est quoi user_has_meubles ? :D

 

Sinon faut que je me renseigne sur OUTER JOIN [:fing fang fung]


Message édité par theredled le 30-05-2007 à 13:28:50

---------------
Contes de fées en yaourt --- --- zed, souviens-toi de ma dernière lettre. --- Rate ta musique
Reply

Marsh Posté le 30-05-2007 à 13:30:44    

count(*) as nb_meubles  as user_has_meubles
:o

Reply

Marsh Posté le 30-05-2007 à 14:27:02    

Mais pourquoi mon

Code :
  1. SUM(CASE meuble_id WHEN NULL THEN 1 ELSE 0) AS nb_nulls,


ne marche pas ? [:sisicaivrai]


Message édité par theredled le 30-05-2007 à 14:28:37

---------------
Contes de fées en yaourt --- --- zed, souviens-toi de ma dernière lettre. --- Rate ta musique
Reply

Marsh Posté le 30-05-2007 à 17:48:04    

parce que toute ta requête est mal pensée :o

Reply

Marsh Posté le 30-05-2007 à 17:48:04   

Reply

Marsh Posté le 30-05-2007 à 18:50:26    

Je vois pas en quoi [:pingouino]


---------------
Contes de fées en yaourt --- --- zed, souviens-toi de ma dernière lettre. --- Rate ta musique
Reply

Marsh Posté le 30-05-2007 à 18:57:16    

déjà, si un user est toujours dans un pays, le premier truc est un inner join et non pas un left.
 
ps : et pour le left, utilise la syntaxe complète : left OUTER join
 
parceque sans outer, ça peut aussi bien vouloir dire left inner (ce qui revient à inner tout court)

Reply

Marsh Posté le 30-05-2007 à 21:55:16    

MagicBuzz a écrit :

déjà, si un user est toujours dans un pays, le premier truc est un inner join et non pas un left.

 

ps : et pour le left, utilise la syntaxe complète : left OUTER join

 

parceque sans outer, ça peut aussi bien vouloir dire left inner (ce qui revient à inner tout court)


En théorie oui un user a toujours un pays, mais en cas de couille on sait jamais, je veux qu'il m'affiche un pays vide. C'est pour ça que pour toutes les requetes administrateur, je met du LEFT JOIN à foison (cet exemple du pays n'est pas le plus probable, mais je fais ça par principe). J'ai peut-etre tort mais je vois pas en quoi ?

 

pour LEFT OUTER JOIN, ça existe en MySQL ça ?


Message édité par theredled le 30-05-2007 à 21:58:03

---------------
Contes de fées en yaourt --- --- zed, souviens-toi de ma dernière lettre. --- Rate ta musique
Reply

Marsh Posté le 31-05-2007 à 00:12:28    

Ben ça devrait exister, c'est la syntaxe SQL Standard... Tu me diras, ils sont bien capable d'avoir décidés de pas l'accepter...

Reply

Marsh Posté le 31-05-2007 à 10:19:40    

alors pourquoi ta requête est mal pensée?
Et bien tu as trois dimensions dans les résultats que tu essayes de remonter:
- pays
- user
- nb de meubles par user ( raccourci en 0 ou plus )
Or:
Pour calculer le nb de meubles par user tu vas logiquement être obligé de grouper sur user_id.
Pour calculer le nb de user par pays, tu vas être obligé de grouper sur pays_id.
Et here comes teh problem. Si tu écris tout dans la même requête tu te retrouves avec un group by pays_id, user_id et ton résultat final n'est pas du tout celui que tu souhaites.
 
Normalement la tu me réponds: "Oui mais j'ai rusé avec somme"... sauf que dans ce cas la c'est nawak [:spamafote]
Tu fais un produit cartésien entre user et meuble et comme tu groupes pas par user tu remontes trop de fois un même user.

Reply

Marsh Posté le 31-05-2007 à 10:36:22    

Je viens de tester une requête qui marche parfaitement.
 
Voici le script complet de test (SQL Server).
 

Code :
  1. CREATE TABLE pays
  2. (
  3.  id int PRIMARY KEY,
  4.  nom varchar(30) NOT NULL
  5. )
  6. go
  7.  
  8. CREATE TABLE uti
  9. (
  10.  id int PRIMARY KEY,
  11.  nom varchar(30) NOT NULL,
  12.  pays_id int NULL FOREIGN KEY REFERENCES pays(id)
  13. )
  14. go
  15.  
  16. CREATE TABLE meuble
  17. (
  18.  id int NOT NULL PRIMARY KEY,
  19.  uti_id int NOT NULL FOREIGN KEY REFERENCES uti(id),
  20.  prix_paye numeric(10,2)
  21. )
  22. go
  23.  
  24. INSERT INTO pays (id, nom) VALUES (1, 'Cuba');
  25. INSERT INTO pays (id, nom) VALUES (2, 'Suisse');
  26. INSERT INTO pays (id, nom) VALUES (3, 'France');
  27.  
  28. INSERT INTO uti (id, nom, pays_id) VALUES (1, 'Pouet Dupont', 2);
  29. INSERT INTO uti (id, nom, pays_id) VALUES (2, 'JP Rafarin', 3);
  30. INSERT INTO uti (id, nom, pays_id) VALUES (3, 'Fidel Castro', 1);
  31. INSERT INTO uti (id, nom, pays_id) VALUES (4, 'Tintin', NULL);
  32. INSERT INTO uti (id, nom, pays_id) VALUES (5, 'Casimir', NULL);
  33. INSERT INTO uti (id, nom, pays_id) VALUES (6, 'J Chirac', 3);
  34. INSERT INTO uti (id, nom, pays_id) VALUES (7, 'S Royal', 3);
  35.  
  36. INSERT INTO meuble (id, uti_id, prix_paye) VALUES (1, 1, 10);
  37. INSERT INTO meuble (id, uti_id, prix_paye) VALUES (2, 2, 99.9);
  38. INSERT INTO meuble (id, uti_id, prix_paye) VALUES (3, 2, 49);
  39. INSERT INTO meuble (id, uti_id, prix_paye) VALUES (4, 1, 29.9);
  40. INSERT INTO meuble (id, uti_id, prix_paye) VALUES (5, 1, 29);
  41. INSERT INTO meuble (id, uti_id, prix_paye) VALUES (6, 4, 50);
  42. INSERT INTO meuble (id, uti_id, prix_paye) VALUES (7, 6, 30);
  43.  
  44.  
  45. SELECT isnull(p.nom, 'Inconnu') pays, count(DISTINCT u.id) tot_users, count(DISTINCT m.uti_id) mob_users
  46. FROM pays p
  47. RIGHT OUTER JOIN uti u ON u.pays_id = p.id
  48. LEFT OUTER JOIN meuble m ON m.uti_id = u.id
  49. GROUP BY p.nom;


 
Sortie :


 
(1 row(s) affected)
 
(1 row(s) affected)
 
(1 row(s) affected)
 
(1 row(s) affected)
 
(1 row(s) affected)
 
(1 row(s) affected)
 
(1 row(s) affected)
 
(1 row(s) affected)
 
(1 row(s) affected)
 
(1 row(s) affected)
 
(1 row(s) affected)
 
(1 row(s) affected)
 
(1 row(s) affected)
 
(1 row(s) affected)
 
(1 row(s) affected)
 
(1 row(s) affected)
 
(1 row(s) affected)
pays                           tot_users   mob_users
------------------------------ ----------- -----------
Inconnu                        2           1
Cuba                           1           0
France                         3           2
Suisse                         1           1
Warning: Null value is eliminated by an aggregate or other SET operation.
 
(4 row(s) affected)
 


Message édité par MagicBuzz le 31-05-2007 à 10:37:53
Reply

Marsh Posté le 31-05-2007 à 10:37:12    

PS : Ca marche grace au Warning, c'est cool :D

Reply

Marsh Posté le 31-05-2007 à 10:43:27    

Bah ouais, truc rarement utilisé, mais pourtant utile :
COUNT(DISTINCT field) => Nombre d'occurences uniques du champ
 
COUNT(outer field) => Lors d'une jointure ouverte, faire un count sur le champ du côté ouvert permet de retrouver le count tel qu'il aurait été avec un INNER JOIN (les NULL sont ignorés lors d'un count)

Message cité 1 fois
Message édité par MagicBuzz le 31-05-2007 à 10:44:12
Reply

Marsh Posté le 31-05-2007 à 10:51:35    

Humm... t'as raison :D
En même temps tu as pris le problème dans un autre sens: "Quels sont les utilisateurs présents dans la table meuble?" alors que je suis resté focalisé sur "Compter les utilisateurs qui ont acheté un meuble ou plus".
 
Force est de constater qu'avec ta méthode on arrive au même résultat, mais de manière bien plus optimale.

Reply

Marsh Posté le 31-05-2007 à 11:50:46    

C'est je pense la plus grande difficulté du SQL...
 
Arriver à formuler de façon "optimale" le problème ;)
Ca vaut souvent de bonnes prises de tête :pt1cable:


Message édité par MagicBuzz le 31-05-2007 à 11:51:06
Reply

Marsh Posté le 31-05-2007 à 12:40:06    

MagicBuzz a écrit :

Bah ouais, truc rarement utilisé, mais pourtant utile :
COUNT(DISTINCT field) => Nombre d'occurences uniques du champ

 

COUNT(outer field) => Lors d'une jointure ouverte, faire un count sur le champ du côté ouvert permet de retrouver le count tel qu'il aurait été avec un INNER JOIN (les NULL sont ignorés lors d'un count)


[:roi] :D

 

Mais bon j'ai 23 autres topics à venir :o

 

Ceci dit je retiens pour plus tard la technique de anapajari qui consiste a joindre une table virtuelle contenant ce qu'il faut :jap:


Message édité par theredled le 31-05-2007 à 12:42:51

---------------
Contes de fées en yaourt --- --- zed, souviens-toi de ma dernière lettre. --- Rate ta musique
Reply

Marsh Posté le    

Reply

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed