SELECT
		bief_depart.b1
        ,bi1.cle_externe AS 'bi1_code_bief'
		,bief_depart.b2
		,bi2.cle_externe AS 'bi2_code_bief' 
		,fanny_tonnage.code_chapitre
		,sum(fanny_tonnage.cumul_tonne)AS tonnage
			FROM bief_depart
			JOIN bief bi1 ON bief_depart.b1=bi1.id_bief
                        JOIN bief bi2 ON bief_depart.b1=bi2.id_bief

			JOIN fanny_tonnage ON (bief_depart.num_quai_depart=fanny_tonnage.num_quai_depart					
AND bief_depart.num_quai_arrive=fanny_tonnage.num_quai_arrive)	

WHERE  IF ( bi1.pk_amont>bi1.pk_aval
				, bi1.pk_amont< IF( bief_depart.pk_b1 < bief_depart.pk_b2, bief_depart.pk_b2,bief_depart.pk_b1  ) 								
						and  bi1.pk_amont>  IF( bief_depart.pk_b1 < bief_depart.pk_b2, bief_depart.pk_b1 ,bief_depart.pk_b2  ) 
				,bi1.pk_aval< IF( bief_depart.pk_b1 < bief_depart.pk_b2, bief_depart.pk_b2,bief_depart.pk_b1  ) 							
						and  bi1.pk_aval>  IF( bief_depart.pk_b1 < bief_depart.pk_b2, bief_depart.pk_b1 ,bief_depart.pk_b2  )
		)		

AND IF ( bi2.pk_amont>bi2.pk_aval
			,bi2.pk_amont< IF( bief_depart.pk_b1 < bief_depart.pk_b2, bief_depart.pk_b2,bief_depart.pk_b1  ) 								
					and  bi2.pk_amont>  IF( bief_depart.pk_b1 < bief_depart.pk_b2, bief_depart.pk_b1 ,bief_depart.pk_b2  ) 
			,bi2.pk_aval< IF( bief_depart.pk_b1 < bief_depart.pk_b2, bief_depart.pk_b2,bief_depart.pk_b1  ) 							
					and  bi2.pk_aval>  IF( bief_depart.pk_b1 < bief_depart.pk_b2, bief_depart.pk_b1 ,bief_depart.pk_b2  ) 
		)
AND bief_depart.pk_b1 != bief_depart.pk_b2
GROUP BY fanny_tonnage.code_chapitre ,bief_depart.b1,bief_depart.b2
ORDER BY bief_depart.b1