0
I’m trying to make a SELECT with a subquery where the field is in the where of subquery is a field of the main table.
However, this field needs to be in single quotes and when I do so, mysql interprets it as a string and not a field. Example:
SELECT t.id, t.code, t.name,
(select date
from positions pt
where pt.code = 't.code'
order by date asc limit 1) as position
FROM trips t
Understand that the field t.code is the field coming from the main table. The field needs to be in single quotes, otherwise the Mysql cannot perform the query.
Because the field needs to be in single quotes?
– viana
This field is an index. When I do not use it in single quotes Mysql understands as an integer value and does not perform the query. When I put the quotes it understands as a string and query normally, even if the fields are of type INT.
– Bruno
I am testing in sqlserver and works normally without quotation marks, but I could not validate in mysql. For me it should work without quotation marks. Error appears?
– viana
My chart has a very large amount of records, about 6 million or so. When I use without quotation marks, I give an Explain and see that it makes the search in the 6 millions, but when I put quotation marks, I can see that it makes the search taking the index and performs the search.
– Bruno
And why don’t you make a Join Inner?
– viana
I tried to join, but still falls in the same situation of the code in simple quotes.
– Bruno
But then @Runo I don’t understand why it needs to be in simple quotes. Maybe I haven’t understood your problem. See this example working perfectly: https://paiza.io/projects/plLZovf48aOOFZGROg3c9w?language=mysql without quotation marks
– viana