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_nomfrom 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 )
innerjoin 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
destinataireset sont strictement confidentiels. Si vous n'etes pas le destinataire du message, il vous est interdit
d'enfaire la copie, de le faire suivre, d'en divulguer le contenu ou de l'utiliser en tout ou partie. Si vous avez
recuce 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,
subirun 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'entiteexpeditrice du message, a moins que cela ait ete clairement specifie dans le message et qu'il soit verifie que
sonauteur 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
andmay 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
ifyou have received this message in error, please notify the sender immediately by return e-mail and delete it from
yoursystem.
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
contextof 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
throughwhich this message is sent unless otherwise clearly indicated in this message and the authority of the author to
sobind the CCF entity referred to is duly verified.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
