SQL Novice - subquery

Question

Hi everybody this is my first question so go easy on me - need some help in extracting some data.

These are the two tables I am working with: Enquiries table:

+---------------------------------------------------------------------+
| ID (Primary Key) | Author | threadid | created     | Comments       |
+---------------------------------------------------------------------+
| 1                |   C    | 237      | 2016-07-24  | Hi there...    |
| 2                |   T    | 421      | 2015-06-07  | Hello, ..      |
| 3                |   C    | 421      | 2015-06-08  | Hi,...         |
| 4                |   C    | 327      | 2017-03-13  | Hey there..    |
+---------------------------------------------------------------------+

Where T stands for company sending an enquiry to a customer and C if a customer sends an enquiry to the company.

Enquirythreads Table:

+----------------------------------+
| ID (Primary Key) |  created      |
+----------------------------------+
| 421              | 2016-07-24    |
| 237              | 2016-07-24    |
| 327              | 2015-06-08    |
+----------------------------------+

The output I would like is:

+---------+
| ID      |
+---------+
|  421    |
+---------+

I want all the enquirythread ids such that the first enquiry associated with it is made by author T.

This is my code but isn't working:

SELECT enquirythreads.id
FROM enquirythreads
JOIN enquiries on enquirythreads.id = enquiries.threadid
WHERE enquiries.threadid IN 
    ( SELECT enquiries.threadid as enqid
      FROM 
            ( SELECT enquiries.threadid, min(enquiries.created) as mincreated
            FROM enquiries
            WHERE enquiries.author = 'T'
            GROUP BY enquiries.threadid ) x

     )

Answer 1

One method uses aggregation and having:

select e.threadid
from enquiries e
group by e.threadid
having min(e.created) = min(case when e.author = 'T' then e.created end)

This says: "check that the earliest created date is the same as the earliest date for 'T'".

Another method uses a correlated subquery in the where clause:

select et.threadid
from enquirythreads et
where (select e2.author
       from enquiries e2
       where e2.threadid = et.threadid
       order by e2.created asc
       fetch first 1 row only
      ) = 'T';