Requête SQL

[codeX]

Bonjour,

C’est une base de données SQLite imposée. En d’autres termes, je ne peux pas changer l’architecture de
l’ensemble car il y aurait beaucoup trop d’autres chose à modifier.

Soit trois tables :

Participant
code : Text
nom : Text
prenom: Text
….. : …….
….. : …….

Lieu
nom : Text (Nom du lieu)
lat : Text (Latitude)
lon : Text (Longitude)

Observation
date : Text (Date SQL)
lieu : Text (Lieu.nom)
p1 : Text (Participant.code)
p2 : Text (Participant.code)
….. : …….
….. : …….

Je veux faire une requête qui me sorte pour une date d’observation précise le nom des participants ainsi que la longitude et latitude du lieu.

J’ai commencé avec un truc du genre :

Code

SELECT Observation.date AS Date, Participant.nom AS Nom, Participant.prenom AS Prenom, Lieu.lat AS Latitude, Lieu.lin AS Longitude
FROM Observation
INNER JOIN Participant ON Observation.p1 = Participant.code
WHERE 1 = 1
AND date = ‘2015-10-11’

Jusqu’ici mes maigres compétences semblent convenir. Là où ça se complique c’est pour récupérer le nom du deuxième participant. Je ne sais vraiment pas comment faire.
Peut-être que faire des jointures n'est pas approprié ?

D’avance merci pour vos lumières

Edit : J'ai oublié de préciser que le nom du deuxième participant peut-être NULL

Ce message a été modifié par codeX - 18 Nov 2015, 10:46.

[roseau]

Bonjour,

C’est une base de données SQLite imposée. En d’autres termes, je ne peux pas changer l’architecture de
l’ensemble car il y aurait beaucoup trop d’autres chose à modifier.

Soit trois tables :

Participant
code : Text
nom : Text
prenom: Text
….. : …….
….. : …….

Lieu
nom : Text (Nom du lieu)
lat : Text (Latitude)
lon : Text (Longitude)

Observation
date : Text (Date SQL)
lieu : Text (Lieu.nom)
p1 : Text (Participant.code)
p2 : Text (Participant.code)
….. : …….
….. : …….

Je veux faire une requête qui me sorte pour une date d’observation précise le nom des participants ainsi que la longitude et latitude du lieu.

J’ai commencé avec un truc du genre :

Code

SELECT Observation.date AS Date, Participant.nom AS Nom, Participant.prenom AS Prenom, Lieu.lat AS Latitude, Lieu.lin AS Longitude
FROM Observation
INNER JOIN Participant ON Observation.p1 = Participant.code
WHERE 1 = 1
AND date = ‘2015-10-11’

Jusqu’ici mes maigres compétences semblent convenir. Là où ça se complique c’est pour récupérer le nom du deuxième participant. Je ne sais vraiment pas comment faire.
Peut-être que faire des jointures n'est pas approprié ?

D’avance merci pour vos lumières

1 Ta requête ne risque pas de ramener le lieu:
'' il faut que tu rajoutes la table "lieu" dans ta jointure si tu veux avoir les lieux
2 c'est quoi : WHERE 1 = 1 ?
3 c'est quoi la structure de la table "observation"? c'est un peu bizarre ce P1, P2 ...

[codeX]

1 Ta requête ne risque pas de ramener le lieu:
'' il faut que tu rajoutes la table "lieu" dans ta jointure si tu veux avoir les lieux
2 c'est quoi : WHERE 1 = 1 ?
3 c'est quoi la structure de la table "observation"? c'est un peu bizarre ce P1, P2 ...

1/ J'ai rajouté effectivement la jointure pour ramener le lieu. Problème de copier/coller
2/ Ça n'a strictement aucune utilité, j'en conviens, si ce n'est de clarifier un peu les requêtes à rallonge en mettant les AND dessous
3/ p1 et p2 contiennent les codes des participants. Il y a juste un autre champ

Je mets de quoi créer une petite base avec des données et la requête qui fonctionne pour me ramener un participant mais pas le deuxième lorsqu'il existe.

Code

BEGIN TRANSACTION;
CREATE TABLE "Participant"(
    "code" Text NOT NULL PRIMARY KEY,
    "nom" Text NOT NULL,
    "prenom" Text NOT NULL );
INSERT INTO `Participant` (code,nom,prenom) VALUES ('P001','Durand','Jacques'),
('P002','Dupont','Pascal'),
('P003','Duglandier','Felix'),
('P004','Motte','Alabelle'),
('P005','Jasper','Benoit');
CREATE TABLE "Observation" (
    `dateObs`    Text NOT NULL,
    `lieu`    Text NOT NULL,
    `p1`    Text NOT NULL,
    `p2`    Text,
    `mesure`    REAL NOT NULL
);
INSERT INTO `Observation` (dateObs,lieu,p1,p2,mesure) VALUES ('2015-09-01','LFPK','P002','P004',5.21),
('2015-09-01','LFEK','P002','P001',12.36),
('2015-09-01','LFDA','P003',NULL,4.58),
('2015-09-01','LFOI','P005','P004',8.99),
('2015-09-02','LFEK','P001','P005',4.87),
('2015-09-02','LFOI','P004',NULL,6.87),
('2015-09-02','LFID','P002','P005',7.54),
('2015-09-02','LFDA','P004','P003',8.63),
('2015-09-02','LFPK','P001','P004',4.78),
('2015-09-03','LFDA','P002','P003',8.21);
CREATE TABLE "Lieu"(
    "nom" Text NOT NULL PRIMARY KEY,
    "lat" Text NOT NULL,
    "lon" Text NOT NULL );
INSERT INTO `Lieu` (nom,lat,lon) VALUES ('LFID','43.907','0.38'),
('LFPK','43.908','0.384'),
('LFDA','43.705','-0.242'),
('LFOI','50.12','1.83'),
('LFEK','43.888','2.037');
COMMIT;

Code

SELECT Observation.dateObs AS 'Date observation',
            Participant.nom AS 'Nom participant #1',
            Lieu.lat AS Latitude,
            Lieu.lon AS Longitude,
            mesure AS Valeur
FROM Observation
INNER JOIN Lieu ON Observation.lieu = Lieu.nom
INNER JOIN Participant ON Observation.p1 = Participant.code
WHERE Observation.dateObs = '2015-09-02'

J'ai essayé ça mais évidemment cela ne marche pas

Code

SELECT Observation.dateObs AS 'Date observation',
            Participant.nom AS 'Nom participant #1',
                        Participant.nom AS 'Nom participant #2',
            Lieu.lat AS Latitude,
            Lieu.lon AS Longitude,
            mesure AS Valeur
FROM Observation
INNER JOIN Lieu ON Observation.lieu = Lieu.nom
INNER JOIN Participant ON Observation.p1 = Participant.code
AND Observation.p2 = Participant.code
WHERE Observation.dateObs = '2015-09-02'

Ce message a été modifié par codeX - 18 Nov 2015, 13:43.

[roseau]

Ta requête ne peux pas fonctionner parce que ton modèle de base de données est mauvais
dans ta table observation il ne faut mettre qu'un code P
ton type de relation n'est pas 1:1
entre participant et observation c'est de type 1:n
entre lieu et observation c'est aussi de type 1:n
il n'y a aucun lien entre participant et lieu
et donc pour avoir
pour avoir P1 et P2 il te faut
1 deux lignes dans la table observation
2 utiliser une jointure à droite ( ou à gauche entre participant et observation et lieu et observation )


pour moi tes tables doivent avoir les structures suivantes avec en gras les clefs:

Participant
code : Text
nom : Text
prenom: Text
….. : …….
….. : …….

Lieu
nom : Text (Nom du lieu)
lat : Text (Latitude)
lon : Text (Longitude)

Observation
date : Text (Date SQL)
code : Text (Participant.code)
nom : Text (Lieu.nom)



et ta requête
SELECT Observation.dateObs AS 'Date observation',
Participant.nom AS 'Nom participant ,
Lieu.lat AS Latitude,
Lieu.lon AS Longitude,
mesure AS Valeur
FROM Observation
INNER JOIN Lieu ON Observation.nom = Lieu.nom
INNER JOIN Observation.code = Participant.code
WHERE Observation.dateObs = '2015-09-02'

aux clauses left ou right près à toi d'essayer

Ce message a été modifié par roseau - 18 Nov 2015, 14:39.

[yponomeute]

C'est un problème "classique" de pivotage de table. Il y a plusieurs méthodes pour y arriver.

Par exemple :

Code

SELECT
    Ob.dateObs AS 'Date observation',
    Participant.nom AS 'Nom participant',
    Lieu.lat AS Latitude,
    Lieu.lon AS Longitude,
    mesure AS Valeur
FROM
    (
        SELECT
            Observation.dateObs,
            Observation.lieu,
            Observation.mesure,
            Observation.p1 AS p0
        FROM
            Observation
        UNION
            SELECT
                Observation.dateObs,
                Observation.lieu,
                Observation.mesure,
                Observation.p2 AS p0
            FROM
                Observation
    ) Ob
INNER JOIN Lieu ON Ob.lieu = Lieu.nom
INNER JOIN Participant ON Ob.p0 = Participant.code
WHERE
    Ob.dateObs = '2015-09-02'

Ce type de solution peut être très gourmand en ressources. Comme l'indique roseau, il y a à la base un problème de structure dans la base de données.

[codeX]

Merci d'avoir pris le temps de regarder ça. Comme précisé dans le premier post, j'hérite de la chose et n'y connait pas grand chose en BDD.
Je vais voir ce que je peux faire et à défaut je m'orienterai vers la solution de yponomeute.