mysql subselect inner join issue

[jshanman]

This is complicated, but it seems that I cannot reference a table inside a subselect inner join

MySQL version 5.0.67

Example

Sub Select Works:
select
product.name,
(select src from images
where images.product_id = product.product_id
order by sort_order desc limit 1)
from product

Sub Select with inner join referencing main table Does not work in MYSQL (but does work in MSSQL!)

select
product.name,
(select lvl from listing
inner join listing_rel on listing_rel.product_id = product.product_id)
)
from product

The error in MYSQL:
#1054 - Unknown column 'product.product_id' in 'on clause'

Please note: this same query works fine in MSSQL

Anyone else come across this?

[physics]

select
product.name,
(select lvl from listing
inner join listing_rel on listing_rel.product_id = product.product_id)
)
from product

It seems like this inner join violates the mysql join syntax.
mysql expects something of the form
select A.x,B.y from A inner join B on A.something = B.something
Whereas you have something more like
select A.x,B.y from A inner join B on B.something = C.something

What exactly do you WANT the query to do - that should be the key to rewriting it into a form mysql can understand.

p.s.
What kind of tables are you using (myisam or innodb)?

[Demaestro]

Looks like the issue is you are joining "listing"
and "listing_rel" but when you define the "join on" you are not referencing the "listing" table you are joining the product table.

Maybe try this

select
product.name,
(select lvl from listing
inner join listing_rel on listing_rel.product_id = listing.product_id where listing_rel.product_id = product.product_id)
)
from product

[jshanman]

Thank you for the replies. The actual query is much more complicated but it turned out that what demaestro suggested worked. I had to move the main table reference to the where clause of the first sub-query because mysql does not seem to be able to see the main table fields in a inner join on a sub query. The same query ran on mssql just fine (we have mssql and mysql synced).

We are using myisam tables because the mysql copy is a readonly version of the mssql database.