Q:

Join query using three tables mysql

I am having three tables p_c_n_details, supplier_details, pcn_type. I tried join query using these tables. But there is an error in group by.
My query:
SELECT pcn_type.name, p_c_n_details.SupplierName, COUNT(p_c_n_details.JPN_ID) 
FROM pcn_type LEFT OUTER JOIN p_c_n_details RIGHT OUTER JOIN supplier_details 
ON p_c_n_details.type = pcn_type.name AND p_c_n_details.SupplierName = 
supplier_details.SupplierName GROUP BY 
pcn_type.name,supplier_details.SupplierName;

pcn_type table:
id    |    name
-------------------------
1          Process Change
2          Design Change
3          EOL

supplier_details table:
id    |    SupplierName
------------------------
1          abc
2          def
3          ghi

p_c_n_details table:
id.   |    SupplierName    |    type           |    JPN_ID
1          abc                  Process Change      0023
2          abc                  Process Change      0024
3          abc                  Process Change      0025
4          abc                  Design Change       0026
5          abc                  Design Change       0027
6          def                  Process Change      0028
7          def                  Process Change      0029
8          def                  EOL                 0030
9          def                  EOL                 0031

Expecting Result:
name            |    supplier  |    total
------------------------------------------------------    
Process Change       abc            03
Design Change        abc            02
EOL                  abc             0
Process Change       def            02
Design Change        def             0
EOL                  def            02

Error I am getting:
#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 
'GROUP BY pcn_type.name,supplier_details.SupplierName LIMIT 0, 25' at line 1

what i need to change in the query to rectify the error.

A:

You can try use query like 
Select pt.name  as name,  sd.SupplierName  as supplier,count(pd.id)
from pcn_type pt 
join   supplier_details sd 
left join   p_c_n_details pd on  pd.type = pt.name and pd.Suppliername = sd.SupplierName 
group by  pt.name,  sd.SupplierName 
order by  sd.SupplierName 

OUTPUT:-

create the environment with table ans sql query where you can directly test the query. Please visit fiddle for more details http://sqlfiddle.com/#!9/d379c3/18

