I'm trying to query a couple of tables with a JOIN using a subquery to narrow down the returned results. When I use a WHERE clause in the subquery which references a value of a column from the outer query I get an error #1054 unknown column in where clause.
I'd like to query the Master table with mode=2 and get relevant records from the Transaction table. If Master.mode=2 and Master.type=1, then get all transactions who's id's match but with type=2 and vice versa. In short, get records from Transaction who's IDs match, but with opposite Type values. In case of more than 1 record, then get the record with the highest 'Amount' from Transaction table.
Master table
+----+-----+-------+-------+
|id |mode |type |other |
+----+-----+-------+-------+
|1 |1 |1 |11111 |
|2 |2 |2 |22222 |
|3 |2 |1 |33333 |
|4 |1 |2 |44444 |
+----+-----+-------+-------+
Transaction table
+----+-------+-------+
|id |type |amount |
+----+-------+-------+
|1 |1 |1000 |
|1 |2 |1000 |
|2 |1 |2000 |
|2 |2 |3000 |
|3 |1 |500 |
|3 |1 |5000 |
|3 |1 |3000 |
|3 |2 |4000 |
|3 |2 |2000 |
|4 |1 |1000 |
|4 |2 |2000 |
|1 |1 |3000 |
+----+-------+-------+
Expected Result
+----+-----+-------+-------+-------+
|id |mode |type |other |amount |
+----+-----+-------+-------+-------+
|2 |2 |2 |22222 |2000 |
|3 |2 |1 |33333 |4000 |
+----+-----+-------+-------+-------+
My query
SELECT t1.*, t2.Amount
FROM master AS t1
INNER JOIN (
SELECT t3.*
FROM transactions AS t3
WHERE t3.id=t1.id AND t3.Type=(CASE WHEN t1.Type=1 THEN 2 ELSE 1 END)
ORDER BY t3.Amount DESC
LIMIT 1
) AS t2 ON t1.id=t2.id
WHERE t1.Mode=2 AND t2.Type=(CASE WHEN t1.Type=1 THEN 2 ELSE 1 END)
I get an error #1054 - Unknown column 't1.id' in 'where clause'
t2
is in theFROM
clause. It's a rowsource for the outer query. MySQL materializes the view as a derived table before the outer query runs. Values from the outer query are not available when the view is materialized. – spencer7593