Requête particulière => passage de liste en colonnes

**CinePhil** · 13/06/2012, 16h51

Et bien il suffit de faire une jointure externe de T_observation_taxon vers les deux autres tables :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT -- les colonnes nécessaires et pas étoile
FROM T_observation_taxon t
LEFT OUTER JOIN TJ_obs_taxon_a_attribut j ON j.OTA_ID_obs_taxon = t.OTA_ID_obs_taxon
	LEFT OUTER JOIN T_attribut_specifique_lot_donnees d ON d.ASL_ID_attribut_lot = j.ASL_ID_attribut_lot

**heretik25** · 14/06/2012, 08h14

Bonjour,

J'ai essayé la requête proposée :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT ota_date_debut,lpo_y_l93,lpo_x_l93
FROM T_observation_taxon t
LEFT OUTER JOIN TJ_obs_taxon_a_attribut j ON j.OTA_ID_obs_taxon = t.OTA_ID_obs_taxon
LEFT OUTER JOIN T_attribut_specifique_lot d ON d.ASL_ID_attribut_lot = j.ASL_ID_attribut_lot

Erreur retournée :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 la colonne « lpo_y_l93 » n'existe pas

Sachant que ce champs est récupéré à partir de la liste d'attributs contenu dans la table "T_attribut_specifique_lot_donnees". Qu'en pensez-vous ?

Merci.

**CinePhil** · 14/06/2012, 09h05

Donne la structure de tes tables.

**heretik25** · 14/06/2012, 13h02

Et voilà

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATE TABLE t_observation_taxon
(
  ota_id_obs_taxon serial NOT NULL,
  iva_id_validation integer,
  ldo_id_lot_donnees integer NOT NULL,
  tao_id_taxon integer NOT NULL,
  ota_commentaire_observation text,
  ota_observateurs character varying(255),
  ota_organismes character varying(255),
  ota_restriction boolean,
  ota_date_debut character varying(8),
  ota_date_fin character varying(8),
  ota_id_observation_base_mere character varying(255) NOT NULL,
  CONSTRAINT c_pk_ota PRIMARY KEY (ota_id_obs_taxon),
  CONSTRAINT c_fk_iva_ota FOREIGN KEY (iva_id_validation)
      REFERENCES t_indice_validation (iva_id_validation) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT c_fk_ldo_ota FOREIGN KEY (ldo_id_lot_donnees)
      REFERENCES t_lot_donnees (ldo_id_lot_donnees) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITH (
  OIDS=FALSE
);

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE tj_obs_taxon_a_attribut
(
  ota_id_obs_taxon integer NOT NULL,
  asl_id_attribut_lot integer NOT NULL,
  tat_valeur_attribut text,
  CONSTRAINT c_pk_tat PRIMARY KEY (ota_id_obs_taxon, asl_id_attribut_lot),
  CONSTRAINT c_fk_asl_tat FOREIGN KEY (asl_id_attribut_lot)
      REFERENCES t_attribut_specifique_lot (asl_id_attribut_lot) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT c_fk_ota_tat FOREIGN KEY (ota_id_obs_taxon)
      REFERENCES t_observation_taxon (ota_id_obs_taxon) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITH (
  OIDS=FALSE
);

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE t_attribut_specifique_lot
(
  asl_id_attribut_lot serial NOT NULL,
  ldo_id_lot_donnees integer NOT NULL,
  asl_nom_attribut_lot character varying(255),
  CONSTRAINT c_pk_asl PRIMARY KEY (asl_id_attribut_lot),
  CONSTRAINT c_fk_ldo_asl FOREIGN KEY (ldo_id_lot_donnees)
      REFERENCES t_lot_donnees (ldo_id_lot_donnees) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITH (
  OIDS=FALSE
);

**CinePhil** · 14/06/2012, 13h18

Il manque la table "T_attribut_specifique_lot_donnees" qui est censée contenir la colonne "lpo_y_l93" !
Cette colonne existe t-elle ?

**heretik25** · 14/06/2012, 13h30

Pardon, elle a juste été renommée en "t_attribut_specifique_lot"

La colonne "lpo_y_l93" n'existe pas en tant que telle. La table "t_attribut_specifique_lot" permet de créer une liste de champs qui ne sont pas communs aux observations.

Ainsi, la table t_attribut_specifique_lot est remplie de cette manière (et c'est ici que se trouve lpo_y_l93) :

1;1;"oha_descriprif"
2;1;"oha_surface"
3;1;"oha_phyto"
4;1;"oha_atteinte"
5;1;"oha_pourcentage"
6;1;"oha_interet_n2000"
13;2;"cbnfc_obs_lot"
14;2;"cbnfc_obs_code_sig"
15;2;"cbnfc_insee"
16;2;"cbnfc_obs_lieu_dit"
17;2;"cbnfc_obs_ref_biblio"
18;2;"cbnfc_obs_source"
21;3;"opie_effectif_male"
22;3;"opie_dept"
...

L'idée étant d'être en mesure de récupérer les champs communs de l'observation mais aussi les champs non communs qui sont stockés en liste. La valeur de ces champs non communs est stockée dans la table. "tj_obs_taxon_a_attribut"

En espérant avoir éclairé un peu la difficulté, merci de donner de votre temps pour m'aider dans cette requête sur laquelle je bloque.

**CinePhil** · 14/06/2012, 13h48

Ainsi, la table t_attribut_specifique_lot est remplie de cette manière (et c'est ici que se trouve lpo_y_l93) :

Je ne vois toujours pas "lpo_y_l93" dans ta liste de données !
C'est l'une des valeurs de la colonne "asl_nom_attribut_lot" ?

Dans ta requête, tu chercherais à récupérer la valeur de l'attribut ayant pour nom "lpo_y_l93" ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT t.ota_date_debut, 
	j.tat_valeur_attribut AS lpo_y_l93
FROM T_observation_taxon t
LEFT OUTER JOIN TJ_obs_taxon_a_attribut j ON j.OTA_ID_obs_taxon = t.OTA_ID_obs_taxon
	LEFT OUTER JOIN T_attribut_specifique_lot d ON d.ASL_ID_attribut_lot = j.ASL_ID_attribut_lot
WHERE d.asl_nom_attribut_lot = 'lpo_y_l93'

**heretik25** · 14/06/2012, 14h16

Et comment faire dans ce cas si je souhaite aussi récupérer "lpo_x_l93" en même temps que "lpo_y_l93" ?

**CinePhil** · 14/06/2012, 14h21

Et bien il faut faire une autre jointure sur la table :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
SELECT t.ota_date_debut, 
	j.tat_valeur_attribut AS lpo_y_l93,
	j2.tat_valeur_attribut AS lpo_x_l93
FROM T_observation_taxon t
LEFT OUTER JOIN TJ_obs_taxon_a_attribut j ON j.OTA_ID_obs_taxon = t.OTA_ID_obs_taxon
	LEFT OUTER JOIN T_attribut_specifique_lot d ON d.ASL_ID_attribut_lot = j.ASL_ID_attribut_lot
LEFT OUTER JOIN TJ_obs_taxon_a_attribut j2 ON j2.OTA_ID_obs_taxon = t.OTA_ID_obs_taxon
	LEFT OUTER JOIN T_attribut_specifique_lot d2 ON d.ASL_ID_attribut_lot = j2.ASL_ID_attribut_lot
WHERE d.asl_nom_attribut_lot = 'lpo_y_l93'
	AND d2.asl_nom_attribut_lot = 'lpo_x_l93'

J'ai actuellement ce genre de truc à faire pour extraire des données venant du logiciel Jira vers une BDD Oracle et j'en suis à 23 jointures sur une table customfieldvalue !

**heretik25** · 14/06/2012, 14h34

Avec cette méthode, j'ai un souci, la valeur du premier attribut "listé" remonte toujours alors que j'interroge un autre champ listé. Une idée du pourquoi ?

Ex :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
606819;"20090904";"6644464";"6644464";"6644464"
606820;"20081113";"6720340";"6720340";"6720340"
606821;"20081113";"6679498";"6679498";"6679498"
606822;"20081208";"6686802";"6686802";"6686802"

Merci

**CinePhil** · 14/06/2012, 14h41

Je ne comprends ce que tu exposes. Quelle est ta requête ?

**heretik25** · 14/06/2012, 14h44

requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT t.ota_id_obs_taxon, t.ota_date_debut, 
	j.tat_valeur_attribut AS lpo_y_l93,
	j2.tat_valeur_attribut AS lpo_x_l93,
	j3.tat_valeur_attribut AS lpo_lieu_dit
 
FROM T_observation_taxon t
 
LEFT OUTER JOIN TJ_obs_taxon_a_attribut j ON j.OTA_ID_obs_taxon = t.OTA_ID_obs_taxon
LEFT OUTER JOIN T_attribut_specifique_lot d ON d.ASL_ID_attribut_lot = j.ASL_ID_attribut_lot
 
LEFT OUTER JOIN TJ_obs_taxon_a_attribut j2 ON j2.OTA_ID_obs_taxon = t.OTA_ID_obs_taxon
LEFT OUTER JOIN T_attribut_specifique_lot d2 ON d.ASL_ID_attribut_lot = j2.ASL_ID_attribut_lot
 
 
LEFT OUTER JOIN TJ_obs_taxon_a_attribut j3 ON j3.OTA_ID_obs_taxon = t.OTA_ID_obs_taxon
LEFT OUTER JOIN T_attribut_specifique_lot d3 ON d.ASL_ID_attribut_lot = j3.ASL_ID_attribut_lot
 
 
WHERE d.asl_nom_attribut_lot = 'lpo_y_l93'
AND d2.asl_nom_attribut_lot = 'lpo_x_l93'
AND d3.asl_nom_attribut_lot = 'lpo_lieu_dit'

Quelque soit l'attribut demandé, la valeur est égale à la première valeur du premier champ listé.

**CinePhil** · 14/06/2012, 14h57

Tu as une petite erreur d'alias dans les jointures.
Dans la condition de jointure, tu pointais à chaque fois sur d.

Mea culpa, cette erreur figurait dans ma requête.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT t.ota_id_obs_taxon, t.ota_date_debut, 
	j.tat_valeur_attribut AS lpo_y_l93,
	j2.tat_valeur_attribut AS lpo_x_l93,
	j3.tat_valeur_attribut AS lpo_lieu_dit
 
FROM T_observation_taxon t
 
LEFT OUTER JOIN TJ_obs_taxon_a_attribut j ON j.OTA_ID_obs_taxon = t.OTA_ID_obs_taxon
	LEFT OUTER JOIN T_attribut_specifique_lot d ON d.ASL_ID_attribut_lot = j.ASL_ID_attribut_lot
 
LEFT OUTER JOIN TJ_obs_taxon_a_attribut j2 ON j2.OTA_ID_obs_taxon = t.OTA_ID_obs_taxon
	LEFT OUTER JOIN T_attribut_specifique_lot d2 ON d2.ASL_ID_attribut_lot = j2.ASL_ID_attribut_lot
 
 
LEFT OUTER JOIN TJ_obs_taxon_a_attribut j3 ON j3.OTA_ID_obs_taxon = t.OTA_ID_obs_taxon
	LEFT OUTER JOIN T_attribut_specifique_lot d3 ON d3.ASL_ID_attribut_lot = j3.ASL_ID_attribut_lot
 
 
WHERE d.asl_nom_attribut_lot = 'lpo_y_l93'
AND d2.asl_nom_attribut_lot = 'lpo_x_l93'
AND d3.asl_nom_attribut_lot = 'lpo_lieu_dit'

**heretik25** · 14/06/2012, 15h05

Effectivement, je n'avais pas fait attention.

Merci en tout cas, j'ai appris quelque chose aujourd'hui

Affaire résolue !

**heretik25** · 11/12/2014, 10h59

Bonjour,

Je remonte le poste car je re-travaille sur cette requête et il semble qu'elle ne remonte la ligne que si tous les attributs sont remplis. Comment être en mesure de remonter toutes les lignes même si un des attributs ne contient pas d'information ?

Merci

**Yellu** · 11/12/2014, 15h32

Salut,

Passes tes conditions de filtrages WHERE (qui portent sur les tables des LEFT JOIN) dans les LEFT JOIN sinon forcement tu perd l'intéret des LEFT JOIN ...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT t.ota_id_obs_taxon, t.ota_date_debut, 
	j.tat_valeur_attribut AS lpo_y_l93,
	j2.tat_valeur_attribut AS lpo_x_l93,
	j3.tat_valeur_attribut AS lpo_lieu_dit
 
FROM T_observation_taxon t
 
LEFT OUTER JOIN TJ_obs_taxon_a_attribut j ON j.OTA_ID_obs_taxon = t.OTA_ID_obs_taxon
LEFT OUTER JOIN T_attribut_specifique_lot d ON d.ASL_ID_attribut_lot = j.ASL_ID_attribut_lot
 
LEFT OUTER JOIN TJ_obs_taxon_a_attribut j2 ON j2.OTA_ID_obs_taxon = t.OTA_ID_obs_taxon
LEFT OUTER JOIN T_attribut_specifique_lot d2 ON d.ASL_ID_attribut_lot = j2.ASL_ID_attribut_lot
 
 
LEFT OUTER JOIN TJ_obs_taxon_a_attribut j3 ON j3.OTA_ID_obs_taxon = t.OTA_ID_obs_taxon
LEFT OUTER JOIN T_attribut_specifique_lot d3 ON d.ASL_ID_attribut_lot = j3.ASL_ID_attribut_lot
 
 
WHERE d.asl_nom_attribut_lot = 'lpo_y_l93'
AND d2.asl_nom_attribut_lot = 'lpo_x_l93'
AND d3.asl_nom_attribut_lot = 'lpo_lieu_dit'

deviendra

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT t.ota_id_obs_taxon, t.ota_date_debut, 
	j.tat_valeur_attribut AS lpo_y_l93,
	j2.tat_valeur_attribut AS lpo_x_l93,
	j3.tat_valeur_attribut AS lpo_lieu_dit
 
FROM T_observation_taxon t
 
LEFT OUTER JOIN TJ_obs_taxon_a_attribut j ON j.OTA_ID_obs_taxon = t.OTA_ID_obs_taxon
LEFT OUTER JOIN T_attribut_specifique_lot d ON d.ASL_ID_attribut_lot = j.ASL_ID_attribut_lot AND d.asl_nom_attribut_lot = 'lpo_y_l93'
 
LEFT OUTER JOIN TJ_obs_taxon_a_attribut j2 ON j2.OTA_ID_obs_taxon = t.OTA_ID_obs_taxon
LEFT OUTER JOIN T_attribut_specifique_lot d2 ON d.ASL_ID_attribut_lot = j2.ASL_ID_attribut_lot AND d2.asl_nom_attribut_lot = 'lpo_x_l93'
 
 
LEFT OUTER JOIN TJ_obs_taxon_a_attribut j3 ON j3.OTA_ID_obs_taxon = t.OTA_ID_obs_taxon
LEFT OUTER JOIN T_attribut_specifique_lot d3 ON d.ASL_ID_attribut_lot = j3.ASL_ID_attribut_lot AND d3.asl_nom_attribut_lot = 'lpo_lieu_dit'

**heretik25** · 11/12/2014, 16h21

Merci pour cette proposition mais apparemment, cela ne remonte pas les bonnes données dans les colonnes (mix des données des attributs)

**Yellu** · 11/12/2014, 16h30

Dans ta modélisation, est-ce que si l'attribut n'est pas renseigné il possède tout de même une ligne dans TJ_obs_taxon_a_attribut ou est-ce que la ligne n'est même pas présente ?

**heretik25** · 11/12/2014, 16h33

Pas de données, pas de ligne dans TJ_obs_taxon_a_attribut

**Yellu** · 11/12/2014, 17h02

Du coup, je tenterai un truc comme ça :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SELECT 
	tob.ota_id_obs_taxon, 
	tob.ota_date_debut, 
	tjotaa1.tat_valeur_attribut AS lpo_y_l93,
	tjotaa2.tat_valeur_attribut AS lpo_x_l93,
	tjotaa3.tat_valeur_attribut AS lpo_lieu_dit
FROM T_observation_taxon tob
INNER JOIN T_attribut_specifique_lot tasl1 
	ON tasl1.ldo_id_lot_donnees = tob.ldo_id_lot_donnees 
	AND tasl1.asl_nom_attribut_lot = 'lpo_y_l93'
INNER JOIN T_attribut_specifique_lot tasl2 
	ON tasl2.ldo_id_lot_donnees = tob.ldo_id_lot_donnees
	AND tasl2.asl_nom_attribut_lot = 'lpo_x_l93'
INNER JOIN T_attribut_specifique_lot tasl3 
	ON tasl3.ldo_id_lot_donnees = tob.ldo_id_lot_donnees
	AND tasl3.asl_nom_attribut_lot = 'lpo_lieu_dit'
LEFT OUTER JOIN TJ_obs_taxon_a_attribut tjotaa1
	ON tjotaa1.OTA_ID_obs_taxon = tob.OTA_ID_obs_taxon
	AND tjotaa1.asl_id_attribut_lot = tasl1.asl_id_attribut_lot
LEFT OUTER JOIN TJ_obs_taxon_a_attribut tjotaa2
	ON tjotaa2.OTA_ID_obs_taxon = tob.OTA_ID_obs_taxon
	AND tjotaa2.asl_id_attribut_lot = tasl2.asl_id_attribut_lot
LEFT OUTER JOIN TJ_obs_taxon_a_attribut tjotaa3
	ON tjotaa3.OTA_ID_obs_taxon = tob.OTA_ID_obs_taxon
	AND tjotaa3.asl_id_attribut_lot = tasl3.asl_id_attribut_lot

A compté que tes attributs soit bien unique même s'ils sont affectés à plusieurs taxons, ce que laisse supposer ta modélisation.

Requête particulière => passage de liste en colonnes

PHP & Base de données

Discussions similaires

Partager

Partager