Find the record with the lest amount of associated records

I just asked this question Find the oldest record in a join between two tables and got a great answer to my question. The problem is that is is not quite what I was looking for (my fault)

Consider the following MySQL tables

Table: Questions
   ID

Table: Results
   ID 
   Created - When this record was added. 
   Q_ID - A FK to the Question table 

Example data

Table: Questions
   ID 
   ----
    1
    8
   15
   55

Table: Results
   ID | Created | Q_ID 
   --------------------
    1 |   12:02 |    1 
    2 |   12:03 |   15 
    3 |   12:04 |    8  

Using the following query, it will return all the records that do not have results associated with them, if all records have results then it will return the question with the oldest result.

SELECT *
FROM
    questions
    LEFT JOIN results
        ON results.q_id = questions.id
ORDER BY
    ISNULL(results.id) DESC, results.created ASC
LIMIT 1

What I am actually looking for is for any question that has not been answered, then to sort the question my the count of how many times they have been answered. The lest answered questions should be at the top.

Answers


This would give you each question, and the number of results associated it (even if no results exist). They will be ordered with the lowest count at the top:

SELECT Questions.ID, COUNT(Results.ID) Result_Count
FROM
    Questions
    LEFT JOIN Results ON Questions.ID = Results.Q_ID
GROUP BY Questions.ID
ORDER BY COUNT(Results.ID)

Is this what you had in mind?


Need Your Help

Outlook Email and Signature from Excel VBA - .Body vs .HTMLbody

vba excel-vba email outlook signature

I have a button on my worksheet to send an email (plus more, but not important). I want my default signature with its HTML formatting but neither option is producing the results I want:

Can we get browser history by using Javascript?

javascript browser browser-history

Can we get browser history for my site with Javascript ?