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

Question

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 !

try like this: `from t1 join t2 on t1.col1 = t2.col1 join t3 on t1.col1 = t3.col1` — Pugal, Jul 18 '19 at 09:10
/*I'D LIKE TO INSERT THE OTHER QUERY HERE!*/ --- a column from 3rd table using join? Or you want to select a value from 3rd table directly (with out joining) here with key reference between one existing column to one column of 3rd table? that case please mention the column names that are related. — mkRabbani, Jul 18 '19 at 09:21
Well, that seems to work. But i just noticed that my first query isn't doing what i want it to do... I'd like to sum the (F_DOCLIGNE.DL_QTE * F_DOCENTETE.Dl_PrixRU) on each row that matches the criteria... Need to correct it... — Thomas Lefloch, Jul 18 '19 at 09:26
@mkRabbani i want to use it as a key reference yes, i want to get only the rows where F_DOCENTETE_CO_NO = F_COLLABORATEUR.CO_NO, in details, i want to take the row if the two strings (name of the collaborator in the project) are equals... I don't need to use the data in the table, just read it, and take it if it matches (it's to prevent errors, if the CO_NO from 'DOCENTETE' doesn't exist in F_COLLABORATEUR.CO_NO, it an error in the DB and i musn't use it) — Thomas Lefloch, Jul 18 '19 at 09:31
Can you add some sample data from your all input tables? also the expected output you wants considering sample data. This would make things easy I think. — mkRabbani, Jul 18 '19 at 09:34
[Data1](https://i.gyazo.com/c0dd35a7c0847a944e7165342ef9690d.png) Here is the output of this code : ```SQL Select F_DOCLIGNE.DO_Piece, (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;``` (the modification of @Pugal) and here is what i should get [Data2](https://i.gyazo.com/1f06a45252b3c1f1758693080e87d73f.png) it should sum all rows with the same DO_piece together, but no. — Thomas Lefloch, Jul 18 '19 at 10:01
@ThomasLefloch Please check the GROUP BY query I have added in the answer section. — mkRabbani, Jul 18 '19 at 12:47
[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 25 years** ago) and its use is discouraged — marc_s, Jul 19 '19 at 19:05

score 0 · Answer 1 · answered Jul 18 '19 at 12:46

Thanks to your answers i managed to correct the first query.

I only need to merge it with the Query(3) now. (I may need help for that too)

Here's the code i made (all the values aren't matching but i think it's a local problem...)

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, F_DOCENTETE.DO_Piece

score 0 · Accepted Answer · answered Jul 18 '19 at 12:47

0

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;

answered Jul 18 '19 at 12:47

mkRabbani

16,295
2
15
24

Thanks you very much, i finally managed to get it, your code confirms me i got it well ! – Thomas Lefloch Jul 18 '19 at 13:00

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

2 Answers2