Hello,
I have a problem with a sql request on Postgresql.
It uses 'left outer join' and 'inner join'
Here is my request:
select a.acte_id,a.acte_libelle,a.acte_url_image,a.acte_page,
a.acte_texte,a.acte_marge_texte,a.acte_date,a.acte_registre_page,
commune.commune_id,commune.commune_nom,commune.commune_nom_equivalent,
departement.departement_id,departement.departement_nom,departement.departement_numero,
pays.pays_id,pays.pays_nom, t.type_acte_id,t.type_acte_nom,
r.registre_id,r.registre_cote,r.registre_libelle,r.registre_annee_debut,
r.registre_annee_fin,r.registre_disponible,r.registre_repertoire,r.registre_transformation,
source.source_id,source.source_libelle,source.source_description,
source.source_cote,source.source_auteur,
archive.archive_id,archive.archive_libelle,archive.archive_libelle_recherche,
adresse_id,adresse_libelle,adresse_libelle1,adresse_libelle2,
commune1.commune_id as commune1_id, commune1.commune_nom as commune1_nom,
commune1.commune_nom_equivalent as commune1_nom_equivalent,
departement1.departement_id as departement1_id,
departement1.departement_nom as departement1_nom,
departement1.departement_numero as departement1_numero,
pays1.pays_id as pays1_id,pays1.pays_nom as pays1_nom
from acte a
left outer join registre as r
on ( a.acte_registre_id = r.registre_id )
inner join commune
on ( a.acte_lieu_id = commune.commune_id )
inner join departement
on ( commune.commune_departement_id = departement.departement_id )
inner join pays
on ( departement.departement_pays_id = pays.pays_id )
inner join type_acte as t
on ( a.acte_type_id=t.type_acte_id )
inner join source
on ( a.acte_source_id=source.source_id )
left outer join archive
on ( source.source_archive_id = archive.archive_id )
inner join adresse
on ( archive.archive_adresse_id = adresse.adresse_id )
left outer join commune as commune1
on ( adresse.adresse_commune_id = commune1.commune_id )
left outer join departement as departement1
on ( commune1.commune_departement_id = departement1.departement_id )
left outer join pays as pays1
on ( departement1.departement_pays_id = pays1.pays_id )
and a.acte_id=1;
It is based on the data model that you can find at the following url:
http://cvs.sourceforge.net/cgi-bin/viewcvs.cgi/jgenea/jgenea-dao/src/sql/hypersonic/crebas-hsql.sql?rev=1.18&content-type=text/vnd.viewcvs-markup
This request must return only one line but the 'left outer join', there are several
lines.
I think that I don't correctly use 'left outer join'...
Is anyone know how to use it?
Thanks by advance for your help.
Thierry
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Ce message et toutes les pieces jointes (ci - apres le \"message\" ) sont etablis a
l'attention exclusive de ses destinataires et sont strictement confidentiels. Si vous
n'etes pas le destinataire du message, il vous est interdit d'en faire la copie, de le
faire suivre, d'en divulguer le contenu ou de l'utiliser en tout ou partie. Si vous
avez recu ce message par erreur, merci d'en avertir immediatement l'expediteur et de
le detruire.
L'integrite du message n'est pas assuree sur Internet, chaque information pouvant etre
interceptee, modifiee, perdue, subir un retard dans sa transmission ou contenir des
virus. L'expediteur decline donc toute responsabilite pour toute alteration,
deformation ou falsification subie par le message au cours de sa transmission.
Toute opinion contenue dans ce message appartient a son auteur et ne peut engager la
responsabilite du CCF ou de l'entite expeditrice du message, a moins que cela ait ete
clairement specifie dans le message et qu'il soit verifie que son auteur etait en
mesure d'engager le CCF ou ladite entite.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This message and any attachments are confidential to the ordinary user of the e-mail
address to which it was addressed and may also be privileged.
If you are not the addressee you may not copy, forward, disclose or use any part of
the message or its attachments and if you have received this message in error, please
notify the sender immediately by return e-mail and delete it from your system.
Internet communications cannot be guaranteed to be secure or error-free as information
could be intercepted, corrupted, lost, arrive late or contain viruses. The sender
therefore does not accept liability for any errors or omissions in the context of this
message which arise as a result of Internet transmission.
Any opinions contained in this message are those of the author and are not given or
endorsed by the CCF or office through which this message is sent unless otherwise
clearly indicated in this message and the authority of the author to so bind the CCF
entity referred to is duly verified.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
