StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You were definitely on the right track to get the result.</p> <p>You are currently missing a few joins in your query. You need to join the <code>applicants</code> to the <code>positions</code> using the <code>applicant_positions</code> table. You can also add a join to the <code>applicant_scores</code> table which will let you use an aggregate function with a <code>CASE</code> to get the columns containing the Interview/Demo Scores:</p> <pre><code>select concat(a.first_name, ' ', a.last_name) ApplicantName, p.title Position, CONCAT(i1.first_name, ' ', i1.last_name) Interviewer, max(case when ac.score_type='Interview 1' then ac.score else '' end) Interview1Score, max(case when ac.score_type='Interview 2' then ac.score else '' end) Interview2Score, max(case when ac.score_type='Demonstration' then ac.score else '' end) DemoScore, DATE_FORMAT(ac.datetime_recorded, '%m-%d-%Y') InterviewDate from applicants a inner join applicant_positions ap on a.id = ap.applicant_id inner join positions p on ap.position_id = p.id inner join applicant_scores ac on a.id = ac.applicant_id inner join interviewers i1 on ac.interviewer_id = i1.id group by concat(a.first_name, ' ', a.last_name), p.title, CONCAT(i1.first_name, ' ', i1.last_name), DATE_FORMAT(ac.datetime_recorded, '%m-%d-%Y'); </code></pre> <p>See <a href="http://sqlfiddle.com/#!2/9fe06/17">SQL Fiddle with Demo</a>. This give the result:</p> <pre><code>| APPLICANTNAME | POSITION | INTERVIEWER | INTERVIEW1SCORE | INTERVIEW2SCORE | DEMOSCORE | INTERVIEWDATE | ------------------------------------------------------------------------------------------------------------------------------- | Jane Doe | Advanced mySQL Programmer | Fred Flintstone | 79 | 92 | | 04-23-2013 | | Jane Doe | Advanced mySQL Programmer | Santa Claus | 86 | | | 04-23-2013 | | John Doe | Advanced mySQL Programmer | Fred Flintstone | 70 | 77 | | 04-23-2013 | | John Doe | Advanced mySQL Programmer | Santa Claus | 80 | 85 | 76 | 04-23-2013 | </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload