CODEWITHSUNDEEP
mysqlsqljoin
Steven Smethurst
Steven Smethurst

Reputation: 4614

Find the oldest record in a join between two tables

I have two tables. "Questions": A list of questions, "Results" The users results to these questions. 

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

I am looking for a query (or two) that will get me the following information.

This query should return question.id=55, because its is the only question that does not have a result. If question.id=55 did not exist then it would return question.id=1 as it has the oldest result to the question.

Upvotes: 1

Views: 1360

Answers (2)

Saboor Awan
Saboor Awan

Reputation: 1585

1- A question that does not have a result associated with it

select q.qid from questions q where q.qid not in ( select r.qid from result r )

2- find the question with the oldest result

select min(r.createdon) from result r

Upvotes: 0

newfurniturey
newfurniturey

Reputation: 38436

If you LEFT JOIN the two tables, you can use the ORDER BY clause to do what you need:

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

This will place any questions that do not have results at the top of the list, followed by a list of all questions with results (in "oldest question first" order). The LIMIT 1 will have it display only the top result - which should match what you need.

Upvotes: 3

Related Questions