Nested SELECT, or am I over-complicating?

75
September 08, 2018, at 10:40 PM

I have the following tables:

table1

id
name
number
status

table2

id
table1_id
transaction_no
transaction_date

I want to return a list of table1.name, table1.num, table2.transaction_no where the table1.status = "Active" and the lastest table2.transaction_date is older than 4 months.

I keep trying something like this but the syntax fails in various places.

SELECT tab1.name, tab1.num, tab2.transaction_no
FROM table1 AS tab1
INNER JOIN table2 AS tab2 ON tab1.id = tab2.table1_id
WHERE tab1.status = "Active"
AND 
(SELECT MAX(tab2.transaction_date) FROM tab2 
 GROUP BY tab1.name) <= (date 4 months ago)

I know this is not right but my brain is struggling to wrap around the idea, or I making it more complicated that it is. Any suggestions or guidance would be greatly appreciated.

Answer 1

That GROUP BY tab1.name doesn't make sense in your subquery. tab1 isn't in context in your subquery (nor istab2 for that matter). Instead though you could do a correlated subquery:

AND (SELECT MAX(t2.transaction_date) FROM table2 t2 WHERE t2.table1_id = tab1.id) <= (date 4 months ago)

Now that subquery can stand on it's own, and it is properly correlated to the main query by way of it's WHERE clause.

If you only want the latest t2 record (and it has to be more than 4 months ago) then a subquery in your FROM clause would work:

SELECT tab1.name, tab1.num, tab2.transaction_no
FROM table1 AS tab1
   INNER JOIN (
    SELECT transaction_no, table1_id 
    FROM table2 t2 
    WHERE transaction_date = (SELECT max(transaction_date) FROM table2 WHERE table1_id = t2.table1_id)
   ) AS tab2 
    ON tab1.id = tab2.table1_id
WHERE tab1.status = "Active";

Here we are still using a correlated subquery to grab the record from table2 that has the greatest transaction_date for each table1_id. Then we are joining that one record per table1_id back to table1.id.

READ ALSO
Batch process a Queryset to avoid database connection timeout

Batch process a Queryset to avoid database connection timeout

I've got a process which imports data from a CSV file then processes the data to run various calculations & set values

110
How to find the nearest geohash in mysql?

How to find the nearest geohash in mysql?

Recently I'm using GeoHash to hash the paired geo-coordinates into a hash value and store it in MySQLNow I want to find the nearest hash given the other hash

135
Can&#39;t expose mysql tcp service running inside kubernetes cluster publicly using nginx-ingress

Can't expose mysql tcp service running inside kubernetes cluster publicly using nginx-ingress

I ran into a problem exposing a mysql database running inside a kubernetes cluster publiclyThe cluster runs with kops on AWS

120
Find next row using on condition in MySQL

Find next row using on condition in MySQL

I have the following table:

76