CODEWITHSUNDEEP
sqlmysqlsubquery
AlexA
AlexA

Reputation: 4118

MYSQL join - reference external field from nested select?

Is it allowed to reference external field from nested select?

E.g. 

SELECT
FROM ext1
LEFT JOIN (SELECT * FROM int2 WHERE int2.id = ext1.some_id ) as x ON 1=1

in this case, this is referencing ext1.some_id in nested select. I am getting errors in this case that field ext1.some_id is unknow. Is it possible? Is there some other way?

UPDATE:

Unfortunately, I have to use nested select, since I am going to add more conditions to it, such as LIMIT 0,1 and then I need to use a second join on the same table with LIMIT 1,1 (to join another row) The ultimate goal is to join 2 rows from the same table as if these were two tables So I am kind of going to "spread" a few related rows into one long row.

Upvotes: 1

Views: 3499

Answers (3)

Peter Lang
Peter Lang

Reputation: 55524

The answer to your initial question is: No, remove your sub-query and put the condition into the ON-clause:

SELECT *
FROM ext1
LEFT JOIN int2 ON ( int2.id = ext1.some_id )

One solution could be to use variables to find the first (or second) row, but this solution would not work efficiently with indexes, so you might end up with performance problems.

SELECT ext1.some_id, int2x.order_col, int2x.something_else
FROM ext1
LEFT JOIN (SELECT `int2`.*, @i:=IF(@id=(@id:=id), @i+1, 0) As rank
           FROM `int2`,
           ( SELECT @i:=0, @id:=-1 ) v
              ORDER BY id, order_col ) AS int2x ON (     int2x.id = ext1.some_id
                                                             AND int2x.rank = 0 )
;

This assumes that you have a column that you want to order by (order_col) and Left Joins the first row per some_id.

Upvotes: 2

lc.
lc.

Reputation: 116498

That's what the ON clause is for:

SELECT
FROM ext1
LEFT JOIN int2 AS x ON x.id = ext1.some_id

Upvotes: 0

Álvaro González
Álvaro González

Reputation: 146460

Do you mean this?

SELECT ...
FROM ext1
LEFT JOIN int2 ON int2.id=ext1.some_id

Upvotes: 0

Related Questions