We help IT Professionals succeed at work.

Select with 3 tables

Ahmad Jaber
Ahmad Jaber asked
on
Good evening everyone,

I have some complications with the query below.
Here is the message I meet, can you help me please?
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'F.Libelle_TAG' in 'on clause'
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'F.Libelle_TAG' in 'on clause' Libellé Quantité P

SELECT
F.Libelle_TAG,
F.Quantite,
F.Prix_Unitaire,
F.Prix_Total,
F.Remise,
F.Total,

R.Coupon,
R.Etat_Commande,

P.Libelle_FR,
P.Libelle_TAG,
P.Contenance_FR

FROM tb_shop_FA F, tb_shop_recap R

LEFT JOIN tb_produits P ON P.Libelle_TAG = F.Libelle_TAG

WHERE R.Coupon <> '0' AND R.Etat_Commande = 'PRÉPARATION'

Open in new window


I had tried this but he too was throwing me an error
SELECT
F.Libelle_TAG,
F.Quantite,
F.Prix_Unitaire,
F.Prix_Total,
F.Remise,
F.Total,

R.Coupon,
R.Etat_Commande,

P.Libelle_FR,
P.Libelle_TAG,
P.Contenance_FR

FROM tb_shop_FA F, tb_shop_recap R, tb_produits P

WHERE F.Libelle_TAG = P.Libelle_TAG AND R.Coupon <> '0' AND R.Etat_Commande = 'PRÉPARATION'

Open in new window


Thank you for your help

Jaber
Comment
Watch Question

David ToddSenior Database Administrator

Commented:
Hi Jaber,

From tb_shop_FA F, tb_shop_recap r

should be something like

from tb_shop_FA f
inner join tb_shop_recap r
    on r.SomeID = f.SomeID
...

otherwise you have a cartesian or square join - that is, every row of f matches with every row of r,

specifically, the error message says that Libelle_TAG isn't a column in table tb_shop_FA

HTH
  David

Author

Commented:
Thank you David Todd for your help, it worked well.
I ask again for your help with the GROUP BY function
 GROUP BY
F.Reference,
F.Libelle_TAG,
F.Quantite,
F.Prix_Unitaire,
F.Prix_Total,
F.Remise,
F.Total,
R.Reference,
R.Coupon,
R.Etat_Commande,
P.Libelle_FR,
P.Libelle_TAG,
P.Contenance_FR 

Open in new window

When I withdraw this code, the list is ordered and of course not grouped.

When I add it, the list is mixed ... but not serious for now. As soon as I delete a line, I get this error:
SQLSTATE [42000]: syntax error or access violation: 1055 Expression n ° 1 of the SELECT list is not in the GROUP BY clause and contains non-aggregated
column 'sivop_dbase.F.Reference' which does not depend functionally on the columns of the GROUP BY clause; it's incompatible with
sql_mode = only_full_group_by


I very often get this error and I don't understand the reason and the real operation of the GROUP BY function.

I tried to delete everything that may not be grouped and leave those who can be grouped, but I still encounter this error.

The objective is to group:
F. Reference, (single value)
R.Reference, (single value)

F.Libelle_TAG, (single value)
P.Libelle_TAG, (single value)

F. Quantity, (COUNT)
F. Unit_Price, (SUMM)
F. Price_Total, (SUMM)
F. Discount, (single value of "15%")
F. Total, (SUMM)

R.Status_Order, (Single value "Preparation")
P.Libelle_FR + P.Contenance_FR (single value)

Open in new window


Here is my final code:
SELECT
F.Reference,
F.Libelle_TAG,
F.Quantite,
F.Prix_Unitaire,
F.Prix_Total,
F.Remise,
F.Total,

R.Reference,
R.Coupon,
R.Etat_Commande,

P.Libelle_FR,
P.Libelle_TAG,
P.Contenance_FR

FROM tb_shop_FA F

INNER JOIN tb_shop_recap R ON R.Reference = F.Reference
LEFT JOIN tb_produits P ON P.Libelle_TAG = F.Libelle_TAG

WHERE R.Coupon <> '0' AND R.Etat_Commande = 'PREPARATION'

Open in new window


You can see the result here : http://www.sivop.com/admin/boutique/siv/invoice.php
David ToddSenior Database Administrator

Commented:
Hi,

Pardon a little reformat, but give this a try:

SELECT
	F.Reference
	, F.Libelle_TAG
	, count( F.Quantite )
	, sum( F.Prix_Unitaire )
	, sum( F.Prix_Total )
	, F.Remise
	, sum( F.Total )
	
	, R.Reference
	, R.Coupon
	, R.Etat_Commande
	
	, P.Libelle_FR
	, P.Libelle_TAG
	, P.Contenance_FR

FROM tb_shop_FA F

INNER JOIN tb_shop_recap R ON R.Reference = F.Reference
LEFT JOIN tb_produits P ON P.Libelle_TAG = F.Libelle_TAG

WHERE 
	R.Coupon <> '0' 
	AND R.Etat_Commande = 'PREPARATION'

-- Group by clause
group by
	F.Reference
	, F.Libelle_TAG
	--, F.Quantite
	--, F.Prix_Unitaire
	--, F.Prix_Total
	, F.Remise
	--, F.Total
	, R.Reference
	, R.Coupon
	, R.Etat_Commande
	, P.Libelle_FR
	, P.Libelle_TAG
	, P.Contenance_FR 	

Open in new window


I think that the count of quantity should be a sum.

Anyway, the FORM of a group by is
select
    key
    , aggrigatefunction( somecolumn )
from sometable
where
    somecondition
group by
    key
;

HTH
  David
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

>> I don't understand the reason and the real operation of the GROUP BY function


When you use an aggregate function in  query, you need to "group" the aggregates by all columns not used in the aggregate.


A list of the aggregate functions:

https://docs.microsoft.com/en-us/sql/t-sql/functions/aggregate-functions-transact-sql?view=sql-server-ver15



So if you SUM, COUNT, ??? one or more columns, the other columns you select are what you are "grouping" by and are considered non-aggregate columns.


In your example those are  Reference and Libelle_TAG.  Your counts and sums are based on those, so you group by them.


Author

Commented:

Thanks again for your help.
I tried this code without modifying it but it returns this error to me :

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' F.Quantite --, F.Prix_Unitaire --, F.Prix_Total , F.Remise --, F.Total , R' at line 32

I think I missed something but I don't know what


SELECT 
	F.Reference 
	, F.Libelle_TAG 
	, SUM(F.Quantite) 
	, SUM(F.Prix_Unitaire) 
	, SUM(F.Prix_Total) 
	, F.Remise 
	, SUM(F.Total) 
	 
	, R.Reference 
	, R.Coupon 
	, R.Etat_Commande 
	 
	, P.Libelle_FR 
	, P.Libelle_TAG 
	, P.Contenance_FR 
 
FROM tb_shop_FA F 
 
INNER JOIN tb_shop_recap R ON R.Reference = F.Reference 
LEFT JOIN tb_produits P ON P.Libelle_TAG = F.Libelle_TAG 
 
WHERE  
	R.Coupon <> '0'  
	AND R.Etat_Commande = 'PRÉPARATION' 
 
-- Group by clause 
GROUP BY 
 
	  F.Reference 
	, F.Libelle_TAG 
	--, F.Quantite 
	--, F.Prix_Unitaire 
	--, F.Prix_Total 
	, F.Remise 
	--, F.Total 
	, R.Reference 
	, R.Coupon 
	, R.Etat_Commande 
	, P.Libelle_FR 
	, P.Libelle_TAG 
	, P.Contenance_FR


I tried this :

GROUP BY 
 
F.Reference 
, F.Libelle_TAG 
, F.Quantite 
, F.Prix_Unitaire 
, F.Prix_Total 
, F.Remise 
, F.Total 
 
, R.Reference 
, R.Coupon 
, R.Etat_Commande 
 
, P.Libelle_FR 
, P.Libelle_TAG 
, P.Contenance_FR

Here's the result



And with that :

F.Reference 
, F.Libelle_TAG 
, SUM(F.Quantite) 
, SUM(F.Prix_Unitaire) 
, SUM(F.Prix_Total) 
, F.Remise 
, SUM(F.Total) 
 
, R.Reference 
, R.Coupon 
, R.Etat_Commande 
 
, P.Libelle_FR 
, P.Libelle_TAG 
, P.Contenance_FR

SQLSTATE[42000]: Syntax error or access violation: 1056 Can't group on 'SUM(F.Quantite)'

Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

>> I think I missed something but I don't know what


Based on the error message, it appears MySQL doesn't like comments in the "group by".


It is easy to reproduce.


If I try what you posted, it shows that error:

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=d3262e29ec1224d87d3bf65f4f090bba


If I remove the comments, I get the expected table not found:

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=7289210197004511d2faf72810026c3e



In the image you posted, you have SUM's in the group by.  That is wrong.

Author

Commented:

-- edited --


I just understood part of the problem, it was to add AS Quantite ....
I no longer have a PHP or SQL error, but the elements are not grouped

SELECT 
	F.Reference 
	, F.Libelle_TAG 
	, SUM(F.Quantite) AS Quantite 
	, F.Prix_Unitaire 
	, SUM(F.Prix_Total) AS Prix_Total 
	, F.Remise 
	, SUM(F.Total) AS Total 
	 
	, R.Reference 
	, R.Coupon 
	, R.Etat_Commande 
	 
	, P.Libelle_FR 
	, P.Libelle_TAG 
	, P.Contenance_FR 
 
FROM tb_shop_FA F 
 
INNER JOIN tb_shop_recap R ON R.Reference = F.Reference 
LEFT JOIN tb_produits P ON P.Libelle_TAG = F.Libelle_TAG 
 
WHERE  
	R.Coupon <> '0'  
	AND R.Etat_Commande = 'PRÉPARATION' 
 
GROUP BY  
  
  F.Reference 
, F.Libelle_TAG 
, F.Remise 
, F.Prix_Unitaire 
 
, R.Reference 
, R.Coupon 
, R.Etat_Commande 
 
, P.Libelle_FR 
, P.Libelle_TAG 
, P.Contenance_FR 
 
ORDER BY P.Libelle_FR 

Perfect ! I think I have solved my worries now!

It was missing in addition to the GROUP BY request the AS for count and sum and finally, putting only the elements to display in the table, taking care to delete the unused elements.

Thank you very much for your help!


SELECT 
F.Libelle_TAG
, F.Remise
, F.Prix_Unitaire
, SUM(F.Quantite) AS Quantite
, SUM(F.Prix_Total) AS Prix_Total
, SUM(F.Total) AS Total


, P.Libelle_FR
, P.Libelle_TAG
, P.Contenance_FR

FROM tb_shop_FA F

INNER JOIN tb_shop_recap R ON R.Reference = F.Reference
LEFT JOIN tb_produits P ON P.Libelle_TAG = F.Libelle_TAG

WHERE
R.Coupon <> '0'
AND R.Etat_Commande = 'PRÉPARATION'

GROUP BY
 
  F.Libelle_TAG
  , P.Libelle_FR
, P.Libelle_TAG
, P.Contenance_FR
, F.Remise
, F.Prix_Unitaire

ORDER BY P.Libelle_FR 


Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

Your image doesn't show all the columns in the select.


Look at the other columns.  Something in one or more of the non-aggregate columns will be different.

Author

Commented:
SELECT 
	F.Libelle_TAG 
	, F.Remise 
	, F.Prix_Unitaire 
	, SUM(F.Quantite) AS Quantite 
	, SUM(F.Prix_Total) AS Prix_Total 
	, SUM(F.Total) AS Total 
	 
	 
	, P.Libelle_FR 
	, P.Libelle_TAG 
	, P.Contenance_FR 
 
FROM tb_shop_FA F 
 
INNER JOIN tb_shop_recap R ON R.Reference = F.Reference 
LEFT JOIN tb_produits P ON P.Libelle_TAG = F.Libelle_TAG 
 
WHERE  
	R.Coupon <> '0'  
	AND R.Etat_Commande = 'PRÉPARATION' 
 
GROUP BY  
  
  F.Libelle_TAG 
  	, P.Libelle_FR 
	, P.Libelle_TAG 
	, P.Contenance_FR 
	, F.Remise 
	, F.Prix_Unitaire 
 
ORDER BY P.Libelle_FR