Databases
StackOverflow2013
Postsdbo
POIs there an easier way to perform this type of query
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POIs there an easier way to perform this type of query
    primarykey
    data
    text
    <p>I have spend most of the day creating this to finally get the result that I am looking for. I am not great with SQL but there must be an easier way to achieve this.</p> <p>The query is</p> <pre><code>SELECT *, Date(review_date + INTERVAL review_period_weeks week) AS next_review_date FROM (SELECT * FROM (SELECT tbl_candidate_temp.id, tbl_candidate_temp.last_name, tbl_candidate_temp.first_name, tbl_candidate_temp.ni_number, tbl_candidate_temp.date_of_birth, tbl_candidate_temp.start_date, tbl_candidate_temp.expected_end_date, tam_company_main.id AS company_id, tam_company_main.name AS company_name, review_period_weeks FROM (SELECT tam_candidate.id, tam_candidate.last_name, tam_candidate.first_name, tam_candidate.ni_number, tam_candidate.date_of_birth, tam_candidate.start_date, tam_candidate.expected_end_date, tam_candidate.company_main_lu, review_period_weeks FROM tam_candidate INNER JOIN (SELECT tam_candidate.id AS id, tam_country.name AS award_country_name, tam_country.review_period_weeks AS review_period_weeks FROM tam_candidate INNER JOIN tam_country ON tam_candidate.award_country_lu = tam_country.id WHERE reviewer = 1) AS tbl_candidate ON tam_candidate.id = tbl_candidate.id) AS tbl_candidate_temp INNER JOIN tam_company_main ON tbl_candidate_temp.company_main_lu = tam_company_main.id) AS tbl_candidate_temp2 LEFT JOIN (SELECT tam_candidate_review.candidate_lu, review_date FROM tam_candidate_review ORDER BY review_date DESC) AS tbl_review_temp ON tbl_candidate_temp2.id = tbl_review_temp.candidate_lu) AS tbl_candidate_temp3 </code></pre> <p>The candidate table contains the main data, eg name, NI number etc</p> <p>The country contains the county names, as well as the number of weeks that reviews must take place in that country</p> <p>The company table contains the names of companies</p> <p>the reviews tables contain a date, and a candidate number</p> <p>reviwer is the id of the currently logged in reviewer.</p> <p>My goal is to have the following columns:</p> <pre><code>id &lt; candidate id last_name first_name ni_number date_of_birth start_date expected_end_date company_id company_name&lt;lookup from company table review_period_weeks&lt;lookup from country candidate_lu review_date &lt; last review next_review_date &lt; last review + review_period_weeks WEEKS </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USGraham
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. POIs there an easier way to perform this type of query
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. CO-Facepalm- Create a [SQL fiddle](http://sqlfiddle.com/) please.
      singulars
      1. POIs there an easier way to perform this type of query
      1. USLeng
    2. COI Think this is what you wanted http://sqlfiddle.com/#!2/9c859/1/0 - I have just found a problem too, it displays a full row for each review, I would like to only include the most recent review. (One row per candidate with next review date calculated based on the most recent review) Thanks
      singulars
      1. POIs there an easier way to perform this type of query
      1. USGraham
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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