Multiple INNER JOIN in sqldatasource for displaying in a gridview

Question

Almost everything is in the title, I'm trying to create a SQLdatasource in for displaying data from several tables in a gridview.

I have three tables in the model :

table lot_poudre with Id (primary key), reference, date_fabrication, ref_fab, masse_reelle, projet_Id (foreign key to the Id of table projet)

table lot_element with Id (primary key), masse, lot_poudre_Id (foreign key to the Id of the lot_poudre table)

table projet with Id, nom

Here is what I've tried which works for displaying data from two tables (lot_poudre and lot_element) :

<asp:SqlDataSource
        ID="SqlDataSourcelotspoudre"
        runat="server"
        ConnectionString="Data Source=GRE032477\SQLEXPRESS;Initial Catalog=db_pour_test;Integrated Security=True;MultipleActiveResultSets=True;Application Name=EntityFramework"
        ProviderName="System.Data.SqlClient"
        SelectCommand=
        "SELECT lot_poudre.Id,  lot_poudre.reference, lot_poudre.date_fabrication, lot_poudre.ref_fab,  lot_poudre.masse_reelle/Sum(lot_element.masse)*100 AS rendement, lot_poudre.masse_reelle AS masse_initiale
        FROM  

            lot_poudre INNER JOIN lot_element 
                ON lot_poudre.Id = lot_element.lot_poudre_Id 


        GROUP BY lot_poudre.Id,lot_poudre.reference, lot_poudre.date_fabrication, lot_poudre.ref_fab,lot_poudre.masse_reelle;"></asp:SqlDataSource>

The problem is when I try to display data from a third table like this (with table projet):

 <asp:SqlDataSource
        ID="SqlDataSourcelotspoudre"
        runat="server"
        ConnectionString="Data Source=GRE032477\SQLEXPRESS;Initial Catalog=db_pour_test;Integrated Security=True;MultipleActiveResultSets=True;Application Name=EntityFramework"
        ProviderName="System.Data.SqlClient"
        SelectCommand=
        "SELECT lot_poudre.Id,  lot_poudre.reference, lot_poudre.date_fabrication, lot_poudre.ref_fab, projet.nom, lot_poudre.masse_reelle/Sum(lot_element.masse)*100 AS rendement, lot_poudre.masse_reelle AS masse_initiale
        FROM projet 
         INNER JOIN 
            lot_poudre INNER JOIN lot_element 
                ON lot_poudre.Id = lot_element.lot_poudre_Id 
         ON projet.Id = lot_poudre.projet_Id

        GROUP BY lot_poudre.Id,lot_poudre.reference, lot_poudre.date_fabrication, lot_poudre.ref_fab,lot_poudre.masse_reelle, projet.nom;"></asp:SqlDataSource>

The error I get says name of object "projet" not valid.

I tried everything and need some help, the final gridviewI am trying to build displays values from 5 tables !!

Thanks

Boid'

Answer 1

Your inner joins are incorrect. Try this sql:

SELECT lot_poudre.Id,  
        lot_poudre.reference, 
        lot_poudre.date_fabrication, 
        lot_poudre.ref_fab, projet.nom, 
        lot_poudre.masse_reelle/Sum(lot_element.masse)*100 AS rendement, 
        lot_poudre.masse_reelle AS masse_initiale
        FROM projet 
         INNER JOIN lot_poudre ON projet.Id = lot_pourde.Id
         INNER JOIN lot_element  ON lot_poudre.Id = lot_element.lot_poudre_Id 
        GROUP BY lot_poudre.Id,lot_poudre.reference, lot_poudre.date_fabrication, lot_poudre.ref_fab,lot_poudre.masse_reelle, projet.nom