Discussion :

[Yali]

Bonjour,

j'ai une requête SQL que je dois modifié mais je n'arrive pas à faire ce que je souhaite.
Je suis sous Postgres.

Ci-joint la requête originale :

Code :

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

1
2
3
4
5
6
7
8
9
select table4.storyhits, table4.noticehits, table4.prog_titre ,
(select sum(cs.xduration) from cm_statistics cs where cs.prog_id = table4.prog_id and mime = 1 ) as sumreal,
(select sum(cs.xduration) from cm_statistics cs where cs.prog_id = table4.prog_id and mime = 2 ) as sumqt,
(select sum(cs.xduration) from cm_statistics cs where cs.prog_id = table4.prog_id and mime = 3 ) as sumwm,
(select sum(cs.xduration) from cm_statistics cs where cs.prog_id = table4.prog_id) as totalstream 
from (select table3.storyhits, table3.noticehits,table3.prog_id, cp.prog_titre from cm_programmes as cp left join (select table1.noticehits,table2.storyhits ,table1.prog_id from (select count(csn_id) as noticehits, prog_id from cm_stats_notice WHERE true group by prog_id) as table1
LEFT OUTER JOIN (select count(css_id) as storyhits , prog_id from cm_stats_storyboards WHERE true group by prog_id) as table2
ON table1.prog_id=table2.prog_id) as table3
on table3.prog_id=cp.prog_id) as table4

Je souhaite affiner la recherche et rajouter un

Code :

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

where cp.catalogue_id in (300,301)

que je pensais inserer entre le "cm_programmes as cp" et le "left join", mais cela ne marche pas.

ma requete devriendrait donc :

Code :

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

1
2
3
4
5
6
7
8
9
select table4.storyhits, table4.noticehits, table4.prog_titre , 
(select sum(cs.xduration) from cm_statistics cs where cs.prog_id = table4.prog_id and mime = 1 ) as sumreal, 
(select sum(cs.xduration) from cm_statistics cs where cs.prog_id = table4.prog_id and mime = 2 ) as sumqt, 
(select sum(cs.xduration) from cm_statistics cs where cs.prog_id = table4.prog_id and mime = 3 ) as sumwm, 
(select sum(cs.xduration) from cm_statistics cs where cs.prog_id = table4.prog_id) as totalstream 
from (select table3.storyhits, table3.noticehits,table3.prog_id, cp.prog_titre from cm_programmes cp where catalogue_id in (300,301) left join (select table1.noticehits,table2.storyhits ,table1.prog_id from (select count(csn_id) as noticehits, prog_id from cm_stats_notice WHERE true group by prog_id) as table1
LEFT OUTER JOIN (select count(css_id) as storyhits , prog_id from cm_stats_storyboards WHERE true group by prog_id) as table2
ON table1.prog_id=table2.prog_id) as table3 
on table3.prog_id=cp.prog_id) as table4

Comment puis je affiner ma recherche correctement ?

Merci par avance.

[vic]

Je le mettrais juste avant ") as table4"

[Yali]

Je le mettrais juste avant ") as table4"

Bonjour,

Ca marche impeccable 8)

Merci bien

a+