Query help

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 = ‘<?php $current_user->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.

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

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

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;

wordpress AT ieonly DOT com

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;