Discussion :

[korben06]

Donc voici mon petit souci j'ai volonterai retiré des champ du select principale pour la longueur donc la ce qui me pose souci c'est les INNER JOIN en bas je me doute que c'est le where en double qui pose souci mais comment faire autrement

Merci d'avance

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
     String myQuery = "SELECT apsoc.id_society, ";
            myQuery += " askpart.id_article,";
            myQuery += " soc.tl_name,";
            myQuery += " apsoc.bo_choice,";
            myQuery += " apsoc.bo_choice_by_vendor";
            myQuery += " FROM tbl_ask_price_society AS apsoc ";
            myQuery += " INNER JOIN tbl_society AS soc ON soc.id_society = apsoc.id_society";
            myQuery += " INNER JOIN tbl_ask_price_article AS askpart ON askpart.id_ask_price_article = apsoc.id_ask_price_article";
 
            myQuery += " INNER JOIN tbl_articles_price_buy AS artpriceb ON artpriceb.id_article = askpart.id_article";
            myQuery += " WHERE askpart.id_askprice = ?apsoc.idaskprice AND artpriceb.dt_price = (SELECT MAX(artpb.dt_price) ";
            myQuery += " FROM tbl_articles_price_buy as artpb where artpb.id_article = askpart.id_article) ";
 
 
            myQuery += " INNER JOIN tbl_articles_price_sell AS artprice ON artprice.id_article = askpart.id_article";
            myQuery += " WHERE askpart.id_askprice = ?apsoc.idaskprice AND artprice.dt_price = (SELECT MAX(artp2.dt_price) ";
            myQuery += " FROM tbl_articles_price_sell as artp2 where artp2.id_article = askpart.id_article) ";

[al1_24]

En réorganisant les éléments de ta requête, ça donne :

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
25
26
27
28
29
30
31
32
SELECT  apsoc.id_society
    ,   askpart.id_article
    ,   soc.tl_name
    ,   apsoc.bo_choice
    ,   apsoc.bo_choice_by_vendor
FROM    tbl_ask_price_society AS apsoc 
    INNER JOIN 
        tbl_society AS soc 
        ON soc.id_society = apsoc.id_society
    INNER JOIN 
        tbl_ask_price_article AS askpart 
        ON askpart.id_ask_price_article = apsoc.id_ask_price_article
    INNER JOIN
        tbl_articles_price_buy AS artpriceb 
        ON artpriceb.id_article = askpart.id_article
    INNER JOIN 
        tbl_articles_price_sell AS artprice 
        ON artprice.id_article = askpart.id_article
WHERE   askpart.id_askprice = apsoc.idaskprice 
    AND EXISTS 
        (   SELECT  1
            FROM    tbl_articles_price_buy as artpb 
            WHERE   artpb.id_article = askpart.id_article
            HAVING  artpriceb.dt_price = MAX(artpb.dt_price) 
        ) 
    AND EXISTS
        (   SELECT  1
            FROM    tbl_articles_price_sell AS artp2 
            WHERE   artp2.id_article = askpart.id_article
            HAVING  artprice.dt_price = MAX(artp2.dt_price)     
        ) 
-- WHERE    askpart.id_askprice = apsoc.idaskprice

Je n'ai pas compris l'utilité de cette dernière ligne qui est en double...
Une erreur de copier coller ?