Sous requete IN avec plus d'un champs [Résolu]

[mickey45]

bonjour à tous,
j'ai 3 tables:
une table stages avec un champs id_lieu
une table lieux avec un champs id et un champs cp (code postal)
une table villes avec un champs c_p (code postal) et deux champs lat et lon (latitude et longitude)

si je veux afficher les stages ayant lieu dans le 92220:

Code :

SELECT * FROM stages WHERE id_lieu IN (SELECT id FROM lieux WHERE cp = '92220')

jusqu'ici tout va bien.
J'utilise cette requête pour connaitre les 10 villes les plus proches:

Code :

1
2
3
SELECT c_p,(6366*acos(cos(radians(".$LAT."))*cos(radians(`ep_villes`.`lat`))*cos(radians(`villes`.`lon`)-radians(".$LONG."))+sin(radians($LAT))*sin(radians(`villes`.`lat`)))) 
AS Proximite FROM villes 
ORDER BY Proximite LIMIT 1,10

Vous me voyez venir, comment afficher non pas les stages ayant lieu dans les 10 villes les plus proches, mais plutôt, les 10 stages les plus proches!

ce qui serait la requête suivante:

Code :

1
2
3
4
5
SELECT * FROM stages WHERE id_lieu IN (SELECT id FROM lieux WHERE cp IN
(
SELECT c_p,(6366*acos(cos(radians(".$LAT."))*cos(radians(`ep_villes`.`lat`))*cos(radians(`villes`.`lon`)-radians(".$LONG."))+sin(radians($LAT))*sin(radians(`villes`.`lat`)))) AS Proximite FROM villes ORDER BY Proximite LIMIT 1,10
)
)

qui bien sûr ne fonctionne pas pour au moins 3 raisons (plusieurs champs dans le select, le order et le limit)

Toute aide sera la bienvenue
Merci d'avance

[punkoff]

Bonjour,


Mettez-vous aux jointures et votre problème sera résolu assez rapidement je pense.
http://sqlpro.developpez.com/cours/sqlaz/jointures/

Dans le cas où un stage peut être dans plusieurs lieux différent le problème sera un peu plus corsé, et là il faudra connaitre votre sgbd pour trouver une solution appropriée.

[CinePhil]

La structure de ta BDD me semble bancale !
Sais-tu qu'une ville peut avoir plusieurs codes postaux et qu'un code postal peut concerner plusieurs villes ?
En plus, un code postal n'est pas une bonne clé.
À moins que ta table des villes soit en fait une table des codes postaux et que les coordonnées géographiques soient celles du barycentre du secteur couvert par le code postal ?

Connais-tu les jointures ?
Ta première requête peut s'écrire ainsi, en évitant la guerre des étoiles :

Code :

1
2
3
4
SELECT les_colonnes_necessaires_et_pas_etoile
FROM stages s
INNER JOIN lieux l ON l.id = s.id_lieu
WHERE l.cp = '92220'

Ta deuxième requête me semble comporter une erreur :

Code :

1
2
3
4
5
6
7
8
9
10
SELECT c_p,(6366*acos(cos(radians(".$LAT.")) 
    * cos(radians(`ep_villes`.`lat`)) 
    * cos(radians(`villes`.`lon`) 
    - radians(".$LONG.")) 
    + sin(radians($LAT)) 
    * sin(radians(`villes`.`lat`)))) 
AS Proximite 
FROM villes 
ORDER BY Proximite 
LIMIT 1,10

Dans la formule, tu utilises la colonne 'lat' de la table 'ep_villes' et cette table ne figure pas dans la partie FROM de ta requête !

Vous me voyez venir, comment afficher non pas les stages ayant lieu dans les 10 villes les plus proches, mais plutôt, les 10 stages les plus proches!

On peut avoir le résultat de SHOW CREATE TABLE sur chaque table ?

[mickey45]

En fait je n'utilise pas le code postal mais le code insee des communes.

merci pour la première requête modifiée.

pour la seconde elle fonctionne, j'ai fait une erreur de copie:

Code :

1
2
3
4
5
6
7
8
9
10
SELECT c_p,(6366*acos(cos(radians(".$LAT.")) 
    * cos(radians(`villes`.`lat`)) 
    * cos(radians(`villes`.`lon`) 
    - radians(".$LONG.")) 
    + sin(radians($LAT)) 
    * sin(radians(`villes`.`lat`)))) 
AS Proximite 
FROM villes 
ORDER BY Proximite 
LIMIT 1,10

[CinePhil]

En fait je n'utilise pas le code postal mais le code insee des communes.

OK je préfère !
Même si ça reste une mauvaise clé !

Dans quelles villes se situent les stages ?
Comme tu n'as toujours pas donné la structure complète des tables, je suis obligé d'imaginer un peu !

Code :

1
2
3
4
5
6
7
SELECT s.id AS id_stage,
    v.cp AS code_ville,
    v.lat AS latitude, 
    v.lon AS longitude
FROM stage s 
INNER JOIN lieux l ON l.id = s.id_lieu
    INNER JOIN ville v ON v.cp = l.cp

Si tu bidouille ça avec ta requête pour trouver les 10 villes les plus proches, tu devrais trouver les 10 stages les plus proches.

[mickey45]

Merci CinePhil,
tu as bien compris la structure de mes table et j'ai bien compris la requête que tu me propose, c'est parfait.
Par contre je parviens pas à bidouiller pour intégrer ma requête de calcul de distance. Si tu peux me donner une piste, j'ai le cerveau qui fume là...

Merci à toi

[CinePhil]

Quel est ton besoin exactement ?
À partir de coordonnées géographiques, connaître les 10 stages les plus proches ?
Si les variables $LAT et $LONG sont les coordonnées de l'endroit considéré, il te suffit je pense de faire cette requête :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
SELECT c_p,(6366 * acos(cos(radians(".$LAT.")) 
    * cos(radians(`villes`.`lat`)) 
    * cos(radians(`villes`.`lon`) 
    - radians(".$LONG.")) 
    + sin(radians($LAT)) 
    * sin(radians(`villes`.`lat`)))) 
AS Proximite 
FROM villes v
INNER JOIN lieux l ON l.cp = v.cp
  INNER JOIN stage s ON s.id_lieu = l.id
ORDER BY Proximite 
LIMIT 1,10

Par rapport à ta requête, le fait de faire la jointure interne vers les stages ne retiendra que les villes où se déroulent des stages.

[mickey45]

j'ai modifié toutes mes requêtes en utilisant les jointures, et cela marche parfaitement. On voit en effet les choses 'différemment' en utilisant cette technique.
Merci encore à toi, et bon week end!