How to join two tables for query report

Frappe Framework
1

While using this code
result = frappe.db.sql("""select `tabStudent Batch Student`.`student_name`,`tabStudent Batch`.`program` from `tabStudent Batch Student` inner join `tabStudent Batch` on `tabStudent Batch Student`.`parent`=`tabStudent Batch`.`name` where `tabStudent Batch`.`program`=%s""",program,as_dict=1)

it showing the folowing error

> Traceback (most recent call last):
>   File "/home/shahid/meeting/frappe-bench/apps/frappe/frappe/app.py", line 55, in application
>     response = frappe.handler.handle()
>   File "/home/shahid/meeting/frappe-bench/apps/frappe/frappe/handler.py", line 19, in handle
>     execute_cmd(cmd)
>   File "/home/shahid/meeting/frappe-bench/apps/frappe/frappe/handler.py", line 40, in execute_cmd
>     ret = frappe.call(method, **frappe.form_dict)
>   File "/home/shahid/meeting/frappe-bench/apps/frappe/frappe/__init__.py", line 898, in call
>     return fn(*args, **newargs)
>   File "/home/shahid/meeting/frappe-bench/apps/frappe/frappe/desk/query_report.py", line 93, in run
>     res = frappe.get_attr(method_name)(frappe._dict(filters))
>   File "/home/shahid/meeting/frappe-bench/apps/erpnext/erpnext/schools/report/course_wise_summary/course_wise_summary.py", line 16, in execute
>     res = get_student_and_program_and_instructor(program)
>   File "/home/shahid/meeting/frappe-bench/apps/erpnext/erpnext/schools/report/course_wise_summary/course_wise_summary.py", line 29, in get_student_and_program_and_instructor
>     result = frappe.db.sql("""select `tabStudent Batch Student`.`student_name`,`tabStudent Batch`.`program` from `tabStudent Batch Student` inner join `tabStudent Batch` on `tabStudent Batch Student`.`parent`=`tabStudent Batch`.`name` where `tabStudent Batch`.`program`=%s""",program,as_dict=1)
>   File "/home/shahid/meeting/frappe-bench/apps/frappe/frappe/database.py", line 137, in sql
>     self._cursor.execute(query, values)
>   File "/home/shahid/meeting/frappe-bench/env/lib/python2.7/site-packages/MySQLdb/cursors.py", line 205, in execute
>     self.errorhandler(self, exc, value)
>   File "/home/shahid/meeting/frappe-bench/env/lib/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
>     raise errorclass, errorvalue
> ProgrammingError: (1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near \'\'parent\': "\'App\'"}\' at line 1')
2

Look up a basic MySQL tutorial on Google/Stack Overflow. Your syntax is wrong.

3

In order to test your sql statement, you can try running it directly on the database. At the command line, go to frappe-bench folder, then launch mysql:

cd ~/frappe-bench bench --site [your site name] mysql

Copy/paste your query at the prompt and replace its end “%s” by ‘aaa’; (don’t forget the semicolon)

For your information, on my dev instance (8.0.22) the query runs fine.

1 Like