MYSQL join - reference external field from nested select?

Question

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.

Peter Lang · Accepted Answer · 2010-06-10 10:57:00Z

2

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.

edited Jun 10, 2010 at 10:57

answered Jun 10, 2010 at 9:42

Peter Lang

55.7k29 gold badges153 silver badges165 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AlexA Over a year ago

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 The ultimate goal is to join 2 rows from the same table as if these were two tables

Álvaro González · Accepted Answer · 2010-06-10 09:43:45Z

0

Do you mean this?

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

answered Jun 10, 2010 at 9:43

Álvaro González

147k45 gold badges282 silver badges378 bronze badges

Comments

lc. · Accepted Answer · 2010-06-10 09:43:49Z

0

That's what the ON clause is for:

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

answered Jun 10, 2010 at 9:43

lc.

117k21 gold badges163 silver badges190 bronze badges

Collectives™ on Stack Overflow

MYSQL join - reference external field from nested select?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related