How correct query for group by when joining table using left join

Question

This query keeps returning the error:

not a GROUP BY expression

Can someone please help me correct my query? I'm not good at joining tables.

select distinct(nama), namabapa, data_saranabapa.nokpbapa, namaibu, data_saranaibu.nokpibu
from ((data_semua_murid
      left join data_saranabapa on data_semua_murid.nokpbapa=data_saranabapa.nokpbapa)
      left join data_saranaibu on data_semua_murid.nokpibu=data_saranaibu.nokpibu)
where kodsekolah='WBA0002'
group by namabapa
order by namabapa asc

you need to group by : nama,namabapa, data_saranabapa.nokpbapa, namaibu, data_saranaibu.nokpibu....see this post::http://stackoverflow.com/questions/13962772/not-a-group-by-expression-error — Suchit kumar, May 22 '15 at 05:08
DISTINCT is NOT a function on a column, it works on the whole selected rows. select distinct(nama), namabapa... eq select distinct nama, namabapa... eq select distinct nama, (namabapa)... — jarlh, May 22 '15 at 05:26
Which dbms is this? The general GROUP BY rule says: If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function. — jarlh, May 22 '15 at 05:42

score 0 · Accepted Answer · answered May 22 '15 at 08:25

You must group by all the columns you select in your query

select distinct(nama), namabapa, data_saranabapa.nokpbapa, namaibu, data_saranaibu.nokpibu
from ((data_semua_murid
      left join data_saranabapa on data_semua_murid.nokpbapa=data_saranabapa.nokpbapa)
      left join data_saranaibu on data_semua_murid.nokpibu=data_saranaibu.nokpibu)
where kodsekolah='WBA0002'
group by nama, namabapa, data_saranabapa.nokpbapa, namaibu, data_saranaibu.nokpibu
order by namabapa asc

How correct query for group by when joining table using left join

1 Answers1