Title: Query help Last modified: August 21, 2016 --- # Query help * Resolved [SheriW7](https://wordpress.org/support/users/sheriw7/) * (@sheriw7) * [12 years, 3 months ago](https://wordpress.org/support/topic/query-help-2/) * I’m trying to do a query to display info in a student report card and I can’t figure out how to do it. It looks like the data I need to retrieve is in multiple tables. The data I need to display on the report card is the sum of the number of correct test points for a given course (e.g. Science) for the current wordpress user. * These are the tables and columns I’m working with (I’m not sure if I need to use all of them): * Table: wp_wp_pro_quiz_master that has a column called Name. The data in the Name column is my courses such as Science 7. * Table: wp_wp_pro_quiz_statistic that has a column called correct_count. The value I need to count or sum up in this column is always a value of “1”. (Student get 1 point for a correct answer). * Table: wp_wp_pro_quiz_statistic_ref that has a column called user_id that appears to have the same values/data as the ID column in table: wp_users. * I’ve been trying things similar to this with no luck: * SELECT (count)correct_count, name FROM wp_wp_pro_quiz_statistic, wp_wp_pro_quiz_statistic_ref, wp_wp_pro_quiz_master, wp_users WHERE name LIKE ‘%Science 7% ‘ AND correct_count = ‘1 ‘ AND ID = ‘ID ?> ‘; * Can someone advise on how to write this? * Thanks, Sheri * [https://wordpress.org/plugins/elisqlreports/](https://wordpress.org/plugins/elisqlreports/) Viewing 6 replies - 1 through 6 (of 6 total) * Plugin Author [Eli](https://wordpress.org/support/users/scheeeli/) * (@scheeeli) * [12 years, 3 months ago](https://wordpress.org/support/topic/query-help-2/#post-4592906) * This is a good start but I need more information to help you JOIN these tables correctly. I will use multiple ???’s in this query in place of what I don’t know and ask you to look at the tables to fill them in: * SELECT COUNT(wp_wp_pro_quiz_statistic.correct_count) AS Score, wp_wp_pro_quiz_master. name FROM (wp_wp_pro_quiz_statistic_ref INNER JOIN wp_wp_pro_quiz_master ON wp_wp_pro_quiz_statistic_ref.??? = wp_wp_pro_quiz_master.???) LEFT JOIN wp_wp_pro_quiz_statistic ON ???.??? = wp_wp_pro_quiz_statistic.??? WHERE wp_wp_pro_quiz_master.name LIKE ‘%Science 7%’ AND wp_wp_pro_quiz_statistic.correct_count = ‘1’ AND wp_wp_pro_quiz_statistic. user_id = ‘ID ?>’ GROUP BY wp_wp_pro_quiz_master.name; * ??? #1. What is the column in wp_wp_pro_quiz_statistic_ref that refers to the Primary Key of the quiz named in the wp_wp_pro_quiz_master table? * ??? #2. What is the Primany Key column in the wp_wp_pro_quiz_master table? * ??? #3. What is the ID column called in wp_wp_pro_quiz_statistic that is used to connect each row to the appropriate quiz named in wp_wp_pro_quiz_master table or referenced in wp_wp_pro_quiz_statistic_ref? * ???.??? #1. What column in which table refers to the ID column in the wp_wp_pro_quiz_statistic table? * If you can answer these questions then you should have a working query. If your query still does not work then please reply with the SQL Error that you get and the full Query and I’ll help you fix it. * Thread Starter [SheriW7](https://wordpress.org/support/users/sheriw7/) * (@sheriw7) * [12 years, 3 months ago](https://wordpress.org/support/topic/query-help-2/#post-4592976) * I tried the query and got this error first under Unsaved Report: * Unknown column ‘wp_wp_pro_quiz_statistic.user_id’ in ‘where clause’ * and this error was in pink above the query: * ERROR: Unknown column ‘wp_wp_pro_quiz_statistic.user_id’ in ‘where clause’ SQL: SELECT COUNT(wp_wp_pro_quiz_statistic.correct_count) AS Score, wp_wp_pro_quiz_master. name FROM (wp_wp_pro_quiz_statistic_ref INNER JOIN wp_wp_pro_quiz_master ON wp_wp_pro_quiz_statistic_ref. statistic_ref_id = wp_wp_pro_quiz_master.id) LEFT JOIN wp_wp_pro_quiz_statistic ON wp_wp_pro_statistic_ref_id.statistic_ref_id = wp_wp_pro_quiz_statistic.statistic_ref_id WHERE wp_wp_pro_quiz_master.name LIKE ‘%Science 7%’ AND wp_wp_pro_quiz_statistic. correct_count = ‘1’ AND wp_wp_pro_quiz_statistic.user_id = ‘1’ GROUP BY wp_wp_pro_quiz_master. name; * So, I changed the table name to wp_wp_pro_quiz_static_ref.user_id and that removed the errors above, but then I received these errors: * This error came under the Unsaved Report: * Unknown column ‘wp_wp_pro_statistic_ref_id.statistic_ref_id’ in ‘on clause’ * and this error was in pink: * ERROR: Unknown column ‘wp_wp_pro_statistic_ref_id.statistic_ref_id’ in ‘on clause’ SQL:SELECT COUNT(wp_wp_pro_quiz_statistic.correct_count) AS Score, wp_wp_pro_quiz_master. name FROM (wp_wp_pro_quiz_statistic_ref INNER JOIN wp_wp_pro_quiz_master ON wp_wp_pro_quiz_statistic_ref. statistic_ref_id = wp_wp_pro_quiz_master.id) LEFT JOIN wp_wp_pro_quiz_statistic ON wp_wp_pro_statistic_ref_id.statistic_ref_id = wp_wp_pro_quiz_statistic.statistic_ref_id WHERE wp_wp_pro_quiz_master.name LIKE ‘%Science 7%’ AND wp_wp_pro_quiz_statistic. correct_count = ‘1’ AND wp_wp_pro_quiz_statistic_ref.user_id = ‘1’ GROUP BY wp_wp_pro_quiz_master. name; * On your ???, this is what I’m seeing in phpMyAdmin: * Table: wp_wp_pro_quiz_statistic_ref – there’s an area under “indexes” that says: PRIMARY with a field: statistic_ref_id * Table: wp_wp_pro_quiz_master – it says PRIMARY with field: id * Table: wp_wp_pro_quiz_statistic – it says PRIMARY with field: statistic_ref_id and question_id * I can send you screenshots of what I’m looking at via email if needed. * Thanks, Sheri * Plugin Author [Eli](https://wordpress.org/support/users/scheeeli/) * (@scheeeli) * [12 years, 3 months ago](https://wordpress.org/support/topic/query-help-2/#post-4592984) * try changing `wp_wp_pro_statistic_ref_id.statistic_ref_id` to `wp_wp_pro_statistic_ref. statistic_ref_id` * If this new query returns no results then please send me a screenshot of some sample data from the wp_wp_pro_quiz_statistic_ref table with column names. * Aloha, Eli * Thread Starter [SheriW7](https://wordpress.org/support/users/sheriw7/) * (@sheriw7) * [12 years, 3 months ago](https://wordpress.org/support/topic/query-help-2/#post-4592989) * I did get another error which is below. What is your email for me to send the screenshot data to? * Thanks, Sheri * ERROR: Unknown column ‘wp_wp_pro_statistic_ref.statistic_ref_id’ in ‘on clause’ SQL:SELECT COUNT(wp_wp_pro_quiz_statistic.correct_count) AS Score, wp_wp_pro_quiz_master. name FROM (wp_wp_pro_quiz_statistic_ref INNER JOIN wp_wp_pro_quiz_master ON wp_wp_pro_quiz_statistic_ref. statistic_ref_id = wp_wp_pro_quiz_master.id) LEFT JOIN wp_wp_pro_quiz_statistic ON wp_wp_pro_statistic_ref.statistic_ref_id = wp_wp_pro_quiz_statistic.statistic_ref_id WHERE wp_wp_pro_quiz_master.name LIKE ‘%Science 7%’ AND wp_wp_pro_quiz_statistic. correct_count = ‘1’ AND wp_wp_pro_quiz_statistic_ref.user_id = ‘1’ GROUP BY wp_wp_pro_quiz_master. name; * Plugin Author [Eli](https://wordpress.org/support/users/scheeeli/) * (@scheeeli) * [12 years, 3 months ago](https://wordpress.org/support/topic/query-help-2/#post-4592991) * wordpress AT ieonly DOT com * Plugin Author [Eli](https://wordpress.org/support/users/scheeeli/) * (@scheeeli) * [12 years, 3 months ago](https://wordpress.org/support/topic/query-help-2/#post-4592997) * try this: * SELECT SUM(wp_wp_pro_quiz_statistic.correct_count) AS Score, wp_wp_pro_quiz_master. name FROM (wp_wp_pro_quiz_statistic_ref INNER JOIN wp_wp_pro_quiz_master ON wp_wp_pro_quiz_statistic_ref. quiz_id = wp_wp_pro_quiz_master.id) LEFT JOIN wp_wp_pro_quiz_statistic ON wp_wp_pro_quiz_statistic_ref. statistic_ref_id = wp_wp_pro_quiz_statistic.statistic_ref_id WHERE wp_wp_pro_quiz_master. name LIKE ‘%Science 7%’ AND wp_wp_pro_quiz_statistic.correct_count = ‘1’ AND wp_wp_pro_quiz_statistic_ref.user_id = ‘1’ GROUP BY wp_wp_pro_quiz_master.name; Viewing 6 replies - 1 through 6 (of 6 total) The topic ‘Query help’ is closed to new replies. * ![](https://ps.w.org/elisqlreports/assets/icon-256x256.png?rev=1231385) * [EZ SQL Reports Shortcode Widget and DB Backup](https://wordpress.org/plugins/elisqlreports/) * [Frequently Asked Questions](https://wordpress.org/plugins/elisqlreports/#faq) * [Support Threads](https://wordpress.org/support/plugin/elisqlreports/) * [Active Topics](https://wordpress.org/support/plugin/elisqlreports/active/) * [Unresolved Topics](https://wordpress.org/support/plugin/elisqlreports/unresolved/) * [Reviews](https://wordpress.org/support/plugin/elisqlreports/reviews/) * 6 replies * 2 participants * Last reply from: [Eli](https://wordpress.org/support/users/scheeeli/) * Last activity: [12 years, 3 months ago](https://wordpress.org/support/topic/query-help-2/#post-4592997) * Status: resolved