[mySQL] LEFT JOIN et alias ?

Vincent Pottier wrote:

Bonjour,
En local, la requête suivante marche bien :
-----
SELECT
e0.book as book,
e0.chapter as chapter,
e0.verse as verse,
e0.txt as t0,
e0.note as n0,
e1.txt as t1,
e1.note as n1,
e2.txt as t2,
e2.note as n2
FROM
verses as e0,
verses as e1,
verses as e2
WHERE
e0.e_id=1
AND e0.book='SF02'
AND e1.e_id=2
AND e0.booká.book
AND e0.chapterá.chapter
AND e0.verseá.verse
AND e2.e_id=3
AND e0.bookâ.book
AND e0.chapterâ.chapter
AND e0.verseâ.verse
ORDER BY book, chapter, verse
-----
Sur le serveur, j'ai des problèmes...
Testé sous phpMyAdmin, mySQL me renvoie des insanités :
-----
The SELECT would examine too many records and probably take a very long
time. Check your WHERE and use SET OPTION SQL_BIG_SELECTS=1 if the
SELECT is ok

C'est normal, ta requete réalise des produits cartésiens avec 3 tables,
en l'occurrence 3 fois la même table... Compte tenu de ta requete une
jointure interne sera plus efficace et apportera plus de clarté sur ce
qui concerne les jointures (liens entre tables, clause JOIN .. ON) et ce
qui concerne le tri (clause WHERE)

SELECT
e0.book as book, e0.chapter as chapter, e0.verse as verse,
e0.txt as t0, e0.note as n0,
e1.txt as t1, e1.note as n1, e2.txt as t2, e2.note as n2

FROM verses AS e0
INNER JOIN verses AS e1
ON (e0.book = e1.book AND
e0.chapter = e1.chapter AND
e0.verse = e1.verse)
INNER JOIN verses AS e2
ON (e0.book = e2.book AND
e0.chapter = e2.chapter AND
e0.verse = e2.verse)

WHERE e0.e_id = 1
AND e0.book = 'SF02'
AND e1.e_id = 2
AND e2.e_id = 3

ORDER BY book, chapter, verse

-----
J'ai essayé d'introduire la requête en ajoutant l'option :
-----
SET OPTION SQL_BIG_SELECTS=1;
SELECT
e0.book as book,
e0.chapter as chapter,
e0.verse as verse,
e0.txt as t0,
e0.note as n0,
e1.txt as t1,
e1.note as n1,
e2.txt as t2,
e2.note as n2
FROM
verses as e0,
verses as e1,
verses as e2
WHERE
e0.e_id=1
AND e0.book='SF02'
AND e1.e_id=2
AND e0.booká.book
AND e0.chapterá.chapter
AND e0.verseá.verse
AND e2.e_id=3
AND e0.bookâ.book
AND e0.chapterâ.chapter
AND e0.verseâ.verse
ORDER BY book, chapter, verse
-----
En local, ça ne marche plus !
J'ai essayé d'optimiser avec des LEFT JOIN
(ça commence à être compliqué pour moi !)
avec la requête :

Pourquoi des LEFT JOIN ?
Tu vas récupérer tous les éléments de e0, même s'ils n'ont pas de
correspondance dans e1 ou e2 !
L'ensemble résultant sera encore plus grand que précédemment !

AMHA, je pense qu'une petite révision s'impose:
http://sqlpro.developpez.com/SQL_AZ_3.html

-----
SELECT
e0.book as book,
e0.chapter as chapter,
e0.verse as verse,
e0.txt as t0,
e0.note as n0,
e1.txt as t1,
e1.note as n1,
e2.txt as t2,
e2.note as n2
FROM
verses as e0,
LEFT JOIN
verses as e1,
verses as e2
ON
AND e0.booká.book
AND e0.chapterá.chapter
AND e0.verseá.verse
AND e0.bookâ.book
AND e0.chapterâ.chapter
AND e0.verseâ.verse
WHERE
e0.e_id=1
AND e1.e_id=2
AND e2.e_id=3
AND e0.book='SF02'
ORDER BY book, chapter, verse
-----
Et j'ai une gentille remarque de mySQL :
-----
#1064 - You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'LEFT JOIN verses AS e1, verses AS e2 ON AND e0.book = e1.book
-----
D'accord, ma doc date un peu ! Promis, je charge la nouvelle !

Le nombre de tables à croiser est variables, selon le nombre de langues
demndées (ça s'appelle une polyglote) : 2 (e0, e1) 3 (e0,e1,e2) etc...
La requête est écrite à la volée.

AMHA, c'est la conception même de la table qui est à revoir !

Peut-on utiliser des alias dans un join ?

Oui

Comment puis-je optimiser cette requête pour que mon hébergeur ne me
fasse plus des remarques désobligeantes ?

J'optimiserai d'abord le modèle de données...

Merci à vous pour vos réponses.

Une dernière chose que j'ai déjà dite ici (ainsi que d'autres
contributeurs), n'hésitez pas à donner les éléments suivants qui
permettent de mieux comprendre le pb et donc d'y apporter un meilleure
réponse :
_ la description de la table
_ la description de ce que vous voulez (langage naturel, requete)
_ un jeu d'essai

Vincent

--
Philippe.

In article <40deab86$0$14846$626a14ce@news.free.fr>,
"Ph. B." <philippe.boucault@voila_N_O.S_P_A_M_.fr> wrote:


Merci Philippe,

produits cartésiens

Je crois deviner ce que c'est mais...

AMHA, je pense qu'une petite révision s'impose:
http://sqlpro.developpez.com/SQL_AZ_3.html

Ce n'est même pas une révision mais une leçon qui s'impose !
J'apprends la programmation sur le tas ! Et si je commence à fréquenter
assiduement la doc mySQL (j'ai tenu ma promesse, j'ai chargé la dernière
version) je connais à peine le site: sqlpro.developpez.com. D'autant que
je suis relié à l'internet par capilaire et non pas du haut débit, avec
des temps de connexion non illimité... donc consulter en ligne: je
limite)

>
> Le nombre de tables à croiser est variables, selon le nombre de langues
> demndées (ça s'appelle une polyglote) : 2 (e0, e1) 3 (e0,e1,e2) etc...
> La requête est écrite à la volée.

AMHA, c'est la conception même de la table qui est à revoir !

La table contiend des textes référencés par book, chapter, verse en
différentes langues (plus exactement différentes éditions e_id décrites
dans une autre table). Ce que je cherche c'est à sélectionner
différentes éditions d'un même texte (book, chapter, verse égaux et e_id
différent) de 2 à 4 éditions différentes en colonnes (latin, français,
anglais par exemple).
Il me semble qu'une table pour contenir ces versets est la solution la
plus économique, avec un champ pour le verset (un pour les notes)

> Peut-on utiliser des alias dans un join ?

Oui

Effectivement j'ai découvert la syntaxe de LEFT JOIN dans la dernière
doc mySQL après avoir posté (temps de chargement de la doc oblige).
J'avais cru qu'une mention LEFT JOIN suffisait même pour plusieurs
jointures.
Mille excuses !

> Comment puis-je optimiser cette requête pour que mon hébergeur ne me
> fasse plus des remarques désobligeantes ?

J'optimiserai d'abord le modèle de données...

> Merci à vous pour vos réponses.

Une dernière chose que j'ai déjà dite ici (ainsi que d'autres
contributeurs), n'hésitez pas à donner les éléments suivants qui
permettent de mieux comprendre le pb et donc d'y apporter un meilleure
réponse :
_ la description de la table
_ la description de ce que vous voulez (langage naturel, requete)
_ un jeu d'essai

Désolé, j'avais cru que la description de la requête était suffisament
explicite

Pour des suggestions d'optimisation :
CREATE TABLE `verses` (
`id` smallint(11) unsigned NOT NULL auto_increment,
`author` smallint(4) unsigned default NULL,
`book` varchar(5) default NULL,
`chapter` smallint(6) default NULL,
`verse` smallint(6) default NULL,
`lg` char(2) default 'fr',
`e_id` smallint(3) unsigned default NULL,
`txt` text,
`note` mediumtext,
PRIMARY KEY (`id`)
) TYPE=MyISAM PACK_KEYS=0 AUTO_INCREMENT)13 ;
INSERT INTO `verses` VALUES (148, 1, 'SF02', 0, 0, 'la', 1, 'CANTICUM
FRATRIS SOLISrVEL LAUDES CREATURARUM', NULL);
INSERT INTO `verses` VALUES (149, 1, 'SF02', 0, 1, 'la', 1, 'Altissimu,
onnipotente bon signore,rtue so le laude la gloria e l'honore net
onne benedictione.n', NULL);
INSERT INTO `verses` VALUES (150, 1, 'SF02', 0, 2, 'la', 1, 'Ad te solo,
altissimo, se konfanornet nullu homo ene dignu te mentovare.rn',
NULL);
INSERT INTO `verses` VALUES (151, 1, 'SF02', 0, 3, 'la', 1, 'Laudato
sie, mi signore, cun tucte le tue creature,rspecialmente messor lo
frate sole,rlo qual'è iorno, et allumini noi per loi.n', NULL);
INSERT INTO `verses` VALUES (152, 1, 'SF02', 0, 4, 'la', 1, 'Et ellu è
bellu e radiante cun grande splendore,rde te, altissimo, porta
significatione.n', NULL);
INSERT INTO `verses` VALUES (153, 1, 'SF02', 0, 5, 'la', 1, 'Laudato si,
mi signore, per sora luna e le stelle,rin celu l'ai formate clarite et
pretiose et belle.n', NULL);
INSERT INTO `verses` VALUES (154, 1, 'SF02', 0, 6, 'la', 1, 'Laudato si,
mi signore, per frate vento,ret per aere et nubilo et sereno et onne
tempo,rper lo quale a le tue creature dai sustentamento.n', NULL);
INSERT INTO `verses` VALUES (155, 1, 'SF02', 0, 7, 'la', 1, 'Laudato si,
mi signore per sor aquarla quale è multo utile et humile et pretiosa et
casta.n', NULL);
INSERT INTO `verses` VALUES (156, 1, 'SF02', 0, 8, 'la', 1, 'Laudato si,
mi signore, per frate focu,rper lo quale enn'allumini la nocte,red
ello è bello et iocundo et robustoso et forte.n', NULL);
INSERT INTO `verses` VALUES (157, 1, 'SF02', 0, 9, 'la', 1, 'Laudato si,
mi signore, per sora nostra matre terra,rla quale ne sustenta et
governa,ret produce diversi fructi con coloriti flori et herba.n',
NULL);
INSERT INTO `verses` VALUES (158, 1, 'SF02', 0, 10, 'la', 1, 'Laudato
si, mi signore, per quelli ke perdonano per lo tuo amore,ret sostengo
infirmitate et tribulatione.n', NULL);
INSERT INTO `verses` VALUES (159, 1, 'SF02', 0, 11, 'la', 1, 'Beati
quelli ke 'l sosterrano in pace,rka da te, altissimo, sirano
incoronati.n', NULL);
INSERT INTO `verses` VALUES (160, 1, 'SF02', 0, 12, 'la', 1, 'Laudato si
mi signore, per sora nostra morte corporale,rda la quale nullu homo
vivente pò skappare.n', NULL);
INSERT INTO `verses` VALUES (161, 1, 'SF02', 0, 13, 'la', 1, 'Guai a
quelli, ke morrano ne le peccata mortali :rbeati quelli ke trovarà ne
le tue sanctissime voluntati,rka la morte secunda nol farrà male.n',
NULL);
INSERT INTO `verses` VALUES (162, 1, 'SF02', 0, 14, 'la', 1, 'Laudate et
benedicete mi signore,ret rengratiate et serviateli cun grande
humilitate.r', NULL);

INSERT INTO `verses` VALUES (1902, 1, 'SF02', 0, 1, 'fr', 2, 'Très haut,
tout-puissant, bon Seigneur,rà toi sont les louanges, la gloire et
l'honneur,ret toute bénédiction.r', NULL);
INSERT INTO `verses` VALUES (1903, 1, 'SF02', 0, 2, 'fr', 2, 'A toi
seul, Très-Haut, ils conviennent,ret nul homme n'est digne de te
nommer.r', NULL);
INSERT INTO `verses` VALUES (1904, 1, 'SF02', 0, 3, 'fr', 2, 'Loué
sois-tu, mon Seigneur, avec toutes tes créatures,rspécialement messire
frère soleil,rqui est le jour, et par lui tu nous illumines.r', NULL);
INSERT INTO `verses` VALUES (1905, 1, 'SF02', 0, 4, 'fr', 2, 'Et il est
beau et rayonnant avec grande splendeur,rde toi, Très-Haut, il porte le
signe.r', NULL);
INSERT INTO `verses` VALUES (1906, 1, 'SF02', 0, 5, 'fr', 2, 'Loué
sois-tu, mon Seigneur,rpour soeur lune et les étoiles, dans le ciel tu
les as forméesrclaires, précieuses et belles.r', NULL);
INSERT INTO `verses` VALUES (1907, 1, 'SF02', 0, 6, 'fr', 2, 'Loué
sois-tu, mon Seigneur, pour frère vent,ret pour l'air et le nuage et
le ciel serein et tous les temps,rpar lesquels à tes créatures tu
donnes soutien.r', NULL);
INSERT INTO `verses` VALUES (1908, 1, 'SF02', 0, 7, 'fr', 2, 'Loué
sois-tu, mon Seigneur, pour soeur eau,rqui est très utile et
humble,ret précieuse et chaste.r', NULL);
INSERT INTO `verses` VALUES (1909, 1, 'SF02', 0, 8, 'fr', 2, 'Loué
sois-tu, mon Seigneur, pour frère feu,rpar lequel tu illumines la
nuit,ret il est beau et joyeux, et robuste et fort.r', NULL);
INSERT INTO `verses` VALUES (1910, 1, 'SF02', 0, 9, 'fr', 2, 'Loué
sois-tu, mon Seigneur, pour soeur notre mère la terre,rqui nous
soutient et nous gouverne,ret produit divers fruitsravec les fleurs
colorées et l'herbe.r', NULL);
INSERT INTO `verses` VALUES (1911, 1, 'SF02', 0, 10, 'fr', 2, 'Loué
sois-tu, mon Seigneur,rpour ceux qui pardonnent par amour pour toiret
supportent maladies et tribulations.r', NULL);
INSERT INTO `verses` VALUES (1912, 1, 'SF02', 0, 11, 'fr', 2, 'Heureux
ceux qui les supporteront en paix,rcar par toi, Très-Haut, ils seront
couronnés.r', NULL);
INSERT INTO `verses` VALUES (1913, 1, 'SF02', 0, 12, 'fr', 2, 'Loué
sois-tu, mon Seigneur,rpour soeur notre mort corporelle,rà qui nul
homme vivant ne peut échapper.r', NULL);
INSERT INTO `verses` VALUES (1914, 1, 'SF02', 0, 13, 'fr', 2, 'Malheur à
ceux qui mourront dans les péchés mortels,rheureux ceux qu'elle
trouvera dans tes très saintes volontés,rcar la seconde mort ne leur
fera pas mal.r', NULL);
INSERT INTO `verses` VALUES (1915, 1, 'SF02', 0, 14, 'fr', 2, 'Louez et
bénissez mon Seigneur,ret rendez-lui grâces et servez-le avec grande
humilité.', NULL);

INSERT INTO `verses` VALUES (2898, 1, 'SF02', 0, 0, 'en', 3, 'The
Canticle of the Creatures', NULL);
INSERT INTO `verses` VALUES (2899, 1, 'SF02', 0, 1, 'en', 3, 'Most High,
all-powerful, good Lord,nYours are the praise, the glory, and the
honor, and all blessing,', NULL);
INSERT INTO `verses` VALUES (2900, 1, 'SF02', 0, 2, 'en', 3, 'To You
alone, Most High, do they belong,nand no human is worthy to mention
Your name.', NULL);
INSERT INTO `verses` VALUES (2901, 1, 'SF02', 0, 3, 'en', 3, 'Praise be
You, my Lord, with all Your creatures,nespecially Sir Brother Sun,nwho
is the day and through whom You give us light.', NULL);
INSERT INTO `verses` VALUES (2902, 1, 'SF02', 0, 4, 'en', 3, 'And he is
beautiful and radiant with great splendor;nand bears a likeness of You,
Most High One.', NULL);
INSERT INTO `verses` VALUES (2903, 1, 'SF02', 0, 5, 'en', 3, 'Praise be
You, my Lord, through Sister Moon and the stars,nin heaven You formed
them clear precious and beautiful.', NULL);
INSERT INTO `verses` VALUES (2904, 1, 'SF02', 0, 6, 'en', 3, 'Praised be
You, my Lord, through Brother wind,nAnd through the air, cloudy and
serene, and every kind of weather,nthrough whom You give sustenance to
Your creatures.', NULL);
INSERT INTO `verses` VALUES (2905, 1, 'SF02', 0, 7, 'en', 3, 'Praised be
You, my Lord, through Sister water,nwho is very useful and humble and
precious and chaste.', NULL);
INSERT INTO `verses` VALUES (2906, 1, 'SF02', 0, 8, 'en', 3, 'Praise be
You, my Lord, through Brother Fire,nthrough whom You light the
night,nand he is beautiful and playful and robust and strong.', NULL);
INSERT INTO `verses` VALUES (2907, 1, 'SF02', 0, 9, 'en', 3, 'Praised be
You, my Lord, through our Sister Mother Earth,nWho sustains and governs
us,nAnd who produces various fruit with colored flowers and herbs.',
NULL);
INSERT INTO `verses` VALUES (2908, 1, 'SF02', 0, 10, 'en', 3, 'Praised
be You, my Lord, through who pardon for Your love,nAnd bear infirmity
and tribulation.', NULL);
INSERT INTO `verses` VALUES (2909, 1, 'SF02', 0, 11, 'en', 3, 'Blessed
are those who endure in peacenfor by You, Most High, shall they be
crowned.', NULL);
INSERT INTO `verses` VALUES (2910, 1, 'SF02', 0, 12, 'en', 3, 'Praised
be You, my Lord, through our Sister Bodily Death,nfrom whom no one can
escape.', NULL);
INSERT INTO `verses` VALUES (2911, 1, 'SF02', 0, 13, 'en', 3, 'Woe to
those who die in mortal sin.nBlessed are those whom death will find in
Your most holy will,nFor the second death shall do them no harm.',
NULL);
INSERT INTO `verses` VALUES (2912, 1, 'SF02', 0, 14, 'en', 3, 'Praise
and bless my Lord and give Him thanksnand serve Him with great
humility.n', NULL);

In article <40deab86$0$14846$626a14ce@news.free.fr>,
"Ph. B." <philippe.boucault@voila_N_O.S_P_A_M_.fr> wrote:

SELECT
e0.book as book, e0.chapter as chapter, e0.verse as verse,
e0.txt as t0, e0.note as n0,
e1.txt as t1, e1.note as n1, e2.txt as t2, e2.note as n2

FROM verses AS e0
INNER JOIN verses AS e1
ON (e0.book = e1.book AND
e0.chapter = e1.chapter AND
e0.verse = e1.verse)
INNER JOIN verses AS e2
ON (e0.book = e2.book AND
e0.chapter = e2.chapter AND
e0.verse = e2.verse)

WHERE e0.e_id = 1
AND e0.book = 'SF02'
AND e1.e_id = 2
AND e2.e_id = 3

ORDER BY book, chapter, verse

J'ai testé (et fait le code php qui génère la requête ). Ça marche très
bien en local. Propre ! Merci.
Mais le serveur me renvoit toujours ses petites phrases assasines (sous
phpMyAdmin, et une exception dans mon code) :

---
requête SQL : 

SELECT e0 .book AS book ,e0 .chapter AS chapter ,e0 .verse AS verse ,e0
.txt AS t0 ,e0 .note AS n0 ,e1 .txt AS t1 ,e1 .note AS n1 ,e2 .txt AS t2
,e2 .note AS n2
FROM verses AS e0
INNER JOIN verses AS e1 ON (e0 .book á .book AND e0 .chapter á
.chapter AND e0 .verse á .verse )
INNER JOIN verses AS e2 ON (e0 .book â .book AND e0 .chapter â
.chapter AND e0 .verse â .verse )
WHERE e0 .e_id ND e1 .e_id *ND e2 .e_id :ND e0 .book ='SF01'
ORDER BY book ,chapter ,verse
LIMIT 0,30

MySQL a répondu:
The SELECT would examine too many records and probably take a very long
time. Check your WHERE and use SET OPTION SQL_BIG_SELECTS=1 if the
SELECT is ok
---
si j'utilise cette option, le serveur s'octroie le droit d'avorter ?
C'est ce que j'avais cru comprendre dans ma précédente doc.

Si je mets dans la clause WHERE e1.book='SF02' plutot que
e0.book='SF02', on limite le nombre de lignes dans les tables
intermédiaire ?

Un petit coup d'oeil sur www.ifrat.net?uf=springs&um=P vous donnera une
idée de ce que je cherche.

Merci pour vos suggestions ...
Vincent

attention :
1) alias et nom d'objet doivent être colés en revanche les connecteurs logique
doivent être séparés !
donc pas "e0 .e_id ND" mais "e0.e_id = 1 AND"

2) je suis l'auteur du site SQLpro et je suis comme toi en RTC donc, faible
débit.... Mon site a donc été conçu pour ne pas être lourd à charger !

A +

--
Frédéric BROUARD, MVP SQL Server. Expert SQL / spécialiste Delphi, web
Livre SQL - col. Référence : http://sqlpro.developpez.com/bookSQL.html
Le site du SQL, pour débutants et pros : http://sqlpro.developpez.com
************************ www.datasapiens.com *************************

Vincent Pottier a écrit:

In article <40deab86$0$14846$626a14ce@news.free.fr>,
"Ph. B." <philippe.boucault@voila_N_O.S_P_A_M_.fr> wrote:

SELECT
e0.book as book, e0.chapter as chapter, e0.verse as verse,
e0.txt as t0, e0.note as n0,
e1.txt as t1, e1.note as n1, e2.txt as t2, e2.note as n2

FROM verses AS e0
INNER JOIN verses AS e1
ON (e0.book = e1.book AND
e0.chapter = e1.chapter AND
e0.verse = e1.verse)
INNER JOIN verses AS e2
ON (e0.book = e2.book AND
e0.chapter = e2.chapter AND
e0.verse = e2.verse)

WHERE e0.e_id = 1
AND e0.book = 'SF02'
AND e1.e_id = 2
AND e2.e_id = 3

ORDER BY book, chapter, verse

J'ai testé (et fait le code php qui génère la requête ). Ça marche très
bien en local. Propre ! Merci.
Mais le serveur me renvoit toujours ses petites phrases assasines (sous
phpMyAdmin, et une exception dans mon code) :

---
requête SQL :

SELECT e0 .book AS book ,e0 .chapter AS chapter ,e0 .verse AS verse ,e0
.txt AS t0 ,e0 .note AS n0 ,e1 .txt AS t1 ,e1 .note AS n1 ,e2 .txt AS t2
,e2 .note AS n2
FROM verses AS e0
INNER JOIN verses AS e1 ON (e0 .book á .book AND e0 .chapter á
.chapter AND e0 .verse á .verse )
INNER JOIN verses AS e2 ON (e0 .book â .book AND e0 .chapter â
.chapter AND e0 .verse â .verse )
WHERE e0 .e_id ND e1 .e_id *ND e2 .e_id :ND e0 .book ='SF01'
ORDER BY book ,chapter ,verse
LIMIT 0,30

MySQL a répondu:
The SELECT would examine too many records and probably take a very long
time. Check your WHERE and use SET OPTION SQL_BIG_SELECTS=1 if the
SELECT is ok
---
si j'utilise cette option, le serveur s'octroie le droit d'avorter ?
C'est ce que j'avais cru comprendre dans ma précédente doc.

Si je mets dans la clause WHERE e1.book='SF02' plutot que
e0.book='SF02', on limite le nombre de lignes dans les tables
intermédiaire ?

Un petit coup d'oeil sur www.ifrat.net?uf=springs&um=P vous donnera une
idée de ce que je cherche.

Merci pour vos suggestions ...
Vincent