sqlsql-serversage-erp

Add a WHERE clause to a JOIN with an argument in a 3rd table


I'm working on a program to transform DATA from a SQL Server Express database to .CSV files, I'm using C#. I'm facing issues with SQL queries.

I need to use a specific criteria to reduce query's range, but this criteria is in another table, called F_COLLABORATEUR (It's in French, but the name doesn't really matter right ?). I need to add this condition to the query WHERE F_DOCENTETE.CO_NO = F_COLLABORATEUR.CO_NO, to match with a last table (3).

This needs to be in a single big query, I searched a bit and tried to make a subquery but I got this error message:

Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows....

Then I tried to use joins, but when I add the 3rd condition it says:

The multi-part identifier "F_COLLABORATEUR" could not be bound

(I'm a beginner with SQL)

SELECT
    F_DOCLIGNE.DL_Qte * F_DOCLIGNE.DL_PrixRU AS prix_revient_total
FROM
    F_DOCLIGNE 
JOIN 
    F_DOCENTETE ON ((F_DOCLIGNE.DO_Piece = F_DOCENTETE.DO_Piece)  
                AND(F_DOCENTETE.DO_PIECE LIKE 'BC%'));

Here is the code working, but without the additional criteria.

SELECT
    F_DOCLIGNE.DL_Qte * F_DOCLIGNE.DL_PrixRU AS prix_revient_total
FROM
    F_DOCLIGNE 
JOIN 
    F_DOCENTETE ON ((F_DOCLIGNE.DO_Piece = F_DOCENTETE.DO_Piece) 
                AND F_DOCENTETE.CO_NO = F_COLLABORATEUR.CO_NO 
                AND (F_DOCENTETE.DO_PIECE LIKE 'BC%'));

Here is the complete ON I'd like to use.

SELECT 
    F_COLLABORATEUR.CO_NOM AS nom_commercial,
    F_DOCENTETE.DO_PIECE AS ref_document,
    F_DOCENTETE.AB_NO AS numero_abonnement, 
    F_DOCENTETE.CA_NUM AS compte_analytique,  
    F_DOCENTETE.CBMODIFICATION AS modified_on, 
    F_DOCENTETE.CT_NUMPAYEUR AS ref_societe_payeur,
    F_DOCENTETE.DO_CONTACT AS contact, 
    F_DOCENTETE.DO_COORD01 AS nom_client_final1, 
    F_DOCENTETE.DO_COORD02 AS nom_client_final2,
    F_DOCENTETE.DO_DATE AS date_emission,
    F_DOCENTETE.DO_DEBUTPERIOD AS debut_periode,
    F_DOCENTETE.DO_FINPERIOD AS fin_periode, 
    F_DOCENTETE.DO_REF AS numero_piece_externe,
    F_DOCENTETE.DO_TIERS AS ref_societe_sage, 
    F_DOCENTETE.DO_TotalTTC - F_DOCENTETE.DO_TotalHTNet AS total_tva,
    F_DOCENTETE.DO_TotalHTNet AS total_ht,
    F_DOCENTETE.DO_TotalTTC AS total_ttc,
    /*I'D LIKE TO INSERT THE OTHER QUERY HERE!*/
    F_DOCENTETE.DO_TYPE AS type_document 
FROM 
    F_DOCENTETE, F_COLLABORATEUR
WHERE 
    F_DOCENTETE.CO_NO = F_COLLABORATEUR.CO_NO 
    AND (F_DOCENTETE.DO_PIECE LIKE 'BC%')

And here is the complete query I have to use (3)

Can't really post query results since the DB I use is confidential. But i just have a table with 1 column and a table with 18 columns, and i want to make one table of 19 columns.

Thanks for your answers !


Solution

  • You can GROUP BY your results as below-

    SELECT F_DOCLIGNE.DO_Piece, 
          SUM( (F_DOCLIGNE.DL_Qte) * (F_DOCLIGNE.DL_PrixRU)) AS prix_revient_total
    FROM F_DOCLIGNE
         JOIN F_DOCENTETE ON((F_DOCLIGNE.DO_Piece = F_DOCENTETE.DO_Piece)
                             AND (F_DOCENTETE.DO_PIECE LIKE 'BC%'))
         JOIN F_COLLABORATEUR ON F_DOCENTETE.Co_No = F_COLLABORATEUR.CO_NO
    GROUP BY F_DOCLIGNE.DO_Piece;