Responses intermixed. See below ...
news <[EMAIL PROTECTED]> wrote on 12/17/2005 03:16:48 PM:
> Hi
>
> I have the following tables:
> CREATE TABLE `livres`(
> `id` int(10) unsigned NOT NULL auto_increment,
> `isbn` varchar(12) NOT NULL default '',
> 'titre' varchar(80) NOT NULL default '',
^ ^
---^-----^ I think these are just typos. Instead of ' they should have
been `
> `auteur` int(10) unsigned NOT NULL default '0',
> `categorie` varchar(60) NOT NULL default '',
> PRIMARY KEY (`id`),
> KEY `auteur` (`auteur`)
> )
> TYPE=MyISAM
>
>
> CREATE TABLE `auteurs` (
> `id` int(10) unsigned NOT NULL auto_increment,
> `nom` varchar(80) NOT NULL default '',
> `prenom` varchar(60) NOT NULL default '',
> PRIMARY KEY (`id`)
> )
> TYPE=MyISAM
>
> I want to produce a list of all livres by categorie ordered by titre
> including the auteur'name
>
> ex: livre.categorie, livre.titre, livre.isbn, concat(auteurs.nom, ",
",
> auteurs.prenom)
>
> I used the following request:
> select all livres.*, auteurs.id as auid, concat(auteurs.nom,', ',
^^^
---------^^^ All is the default behavior. You are not gaining anything by
adding this to the query.
> auteurs.prenom) as nomauteur from livres, auteurs where auteurs.id =
> livres.auteur group by livres.categorie order by livres.titre asc;
^^^^^ (1) ^^^(2)
(1) what are you GROUP-ing? You are not summarizing data in any way and
you have more columns not part of aggregate functions in your SELECT
statement than you list in your GROUP BY clause. This may be part of your
problem as you will get only one row per category the way it is currently
written.
(2) ASC is the default sorting order. Not wrong, just not necessary.
>
> It returns only 1 line instead of all the lines in livres .
>
> Why?
>
>
> Thanks
>
You are creating an implicit INNER JOIN so that means that you must have
matching rows in BOTH tables for the JOIN condition to be true and the row
to be part of the result.
<rant>I _REALLY_ wish the JOIN examples in the manual would change to stop
demonstrating this technique. It causes many problems for the newer users
if they do not bother to learn the EXPLICIT form first.</rant>
Here is how I prefer to write the query you wrote (using an explicit JOIN)
SELECT livres.*
, auteurs.id as auid
, CONCAT(auteurs.nom,', ',auteurs.prenom) as nomauteur
FROM livres
INNER JOIN auteurs
ON auteurs.id = livres.auteur
ORDER BY livres.titre;
That would find all books with auteurs, however it would not locate any
books without auteurs. To list all books regardless of whether they have a
matching auteur, you need to use one of the OUTER joins like this:
SELECT livres.*
, auteurs.id as auid
, CONCAT(auteurs.nom,', ',auteurs.prenom) as nomauteur
FROM livres
LEFT JOIN auteurs
ON auteurs.id = livres.auteur
ORDER BY livres.titre;
That will list every book and any auteurs should they exist. Please let me
know if this gets you going.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
