JOIN a table by SELECT subquery with LIMIT 1 and WHERE clause matching column values outside of it

Question

I'm trying to JOIN a table with a subquery to limit data to only 1 last row, matching certain values from other FROM or JOINed tables:

SELECT

  t1.column1,
  t1.column2,
  t2.column1,
  t3.column2

FROM

  table1 t1

JOIN 
(
    SELECT column1 
    FROM table2 
    WHERE t1.column2 > table2.column1
    ORDER BY table2.date DESC
    LIMIT 1

) t2 

JOIN table3 t3 ON t2.column1=t3.column2

Getting an error: Unknown column t1.column2 in 'where clause'. Seems I can't address other columns inside the subquery.

Is what I'm trying to do possible? If so, what am I doing wrong / what other way could I try this?

I need to join only a single row, not the whole table. Putting the where clause outside would include all possible matches from the JOINed table, but I only need a specific one. @Dai — Glorious Kale, Oct 30 '19 at 09:54

Ankit Bajpai · Answer 1 · 2019-10-30T10:53:38.237

0

You may re-write your query as -

SELECT t1.column1,
       t1.column2,
       t2.column1,
       t3.column2
FROM table1 t1
JOIN (SELECT column1 
      FROM table2
      ORDER BY table2.date DESC
      LIMIT 1) t2 ON t1.column2 > t2.column1
JOIN table3 t3 ON t2.column1=t3.column2

edited Oct 30 '19 at 10:53

answered Oct 30 '19 at 10:01

Ankit Bajpai

13,128
4
25
40

Thanks for responding! The value t2.column1 is blank, SELECT column1 FROM table2 LIMIT 1 would only join the first row from t2, regardless of filtering the data? Note that I've slightly edited the query in my question – Glorious Kale Oct 30 '19 at 10:08
1

@IvanIvković, Please try now. – Ankit Bajpai Oct 30 '19 at 10:54
found the solution, you may check the new answer – Glorious Kale Oct 30 '19 at 11:00

score 0 · Answer 2 · answered Oct 30 '19 at 10:13

0

In MySQL, implicit and explicit joins together in a single query create the problem. You can not use an implicit JOIN based on a nested SELECT - FROM - WHERE query when there is another explicit JOIN present.

Try below.

SELECT *
FROM table1 t1
INNER JOIN  table2 t2 ON t1.column2 > t2.column1
LEFT OUTER JOIN table3 t3 ON t2.column1=t3.column2

answered Oct 30 '19 at 10:13

Shishir Kumar

7,981
3
29
45

The problem is I need to join only the last row matching a few parameters, not the whole table. – Glorious Kale Oct 30 '19 at 10:20

Glorious Kale · Accepted Answer · 2019-10-30T11:15:05.703

0

The solution for me was:

SELECT

  t1.column1,
  t1.column2,
  t2.column1,
  t3.column2

FROM

  table1 t1

JOIN table2 t2 ON t2.id = 
(
    SELECT id 
    FROM table2 
    WHERE t1.column2 > table2.column1
    ORDER BY table2.date DESC
    LIMIT 1
) 

JOIN table3 t3 ON t2.column1=t3.column2

More info on:

CROSS/OUTER APPLY in MySQL

https://dba.stackexchange.com/a/170760/13156

edited Oct 30 '19 at 11:15

answered Oct 30 '19 at 10:55

Glorious Kale

1,273
15
25

JOIN a table by SELECT subquery with LIMIT 1 and WHERE clause matching column values outside of it

3 Answers3