0

To everyone who have familiarity with Open Journal System (OJS) or master of mysql query kindly need your help to generate a report based on the selected field from multiple tables. Here is the Tables:

submission: submission_id, date_submitted, last_modified, status, stage_id

submission_setting: submission_id, setting_name(some kind of submission type), setting_value(value of setting name)

author: submission_id, author_id, first_name, last_name, email

review assignment: review_id, submission_id, reviewer_id, recommendation

users: user_id, first_name, last_name

So i want a report of all articles that ever submitted to the OJS with the time it submitted, status of submission, the author of the article, also the reviewer who review the article (if there's no review it should be null) and the recommendation from the reviewer.

I tried to do the query, but it only produce list of article who just have the review (it's not generate article who still not or dont have the review). Here is my query:

SELECT DISTINCT s.submission_id, p.setting_value, a.first_name, a.last_name, s.date_submitted, s.last_modified, s.status, s.stage_id, u.first_name, u.last_name, r.recommendation from submissions s, submission_settings p, authors a, users u, review_assignments r WHERE p.submission_id = s.submission_id AND a.submission_id = s.submission_id AND u.user_id = r.reviewer_id AND r.submission_id = s.submission_id AND p.setting_name = 'title' GROUP BY s.submission_id;
Community
  • 1
  • 1
Prasetya Aditya
  • 1
  • A group by with distinct is not a useful combination (group by is distinct) and you should not use a group by with no aggregate functions. Also you should read up on joins and replace the outmoded comma joins with explicit joins - read up on left joins specifically. – P.Salmon Feb 17 '20 at 08:44
  • I tried with Select without distinct and the result still same, so do you think i should use left join in this case? – Prasetya Aditya Feb 17 '20 at 08:52
  • the point of a left join is that it will return everything from the left side of the join even if there is nothing on the right side so yes, but you should not mix comma and explicit joins.I would also remove the distinct and the group by until you have the joins sorted out. – P.Salmon Feb 17 '20 at 08:55
  • Ok ill try, thanks for the response. Much appreciated – Prasetya Aditya Feb 17 '20 at 09:27

0 Answers0