mysql - Query with LEFT JOIN in a specific order -

i have table called products with:

productid name 

and table called product_images with:

imageid productid filename image_type image_order 

the image_type can 1 of 2 options (a or b) , order allows multiple images of each type shown in specific order.

the query using like:

select productid,        name,         ifnull((select filename                   product_images                  productid = products.productid               order image_type desc,                        image_order                  limit 1),              'no_image.jpg') image_name    products   productid in (966, 967, 968) 

note: list of prodictid's specified search results.

this working fine until added field product_images table alt/title text.

so, tried following

   select productid,            name,           pi1.filename,            pi1.alttag      products  left join (select *               product_images              product_images.productid = products.productid           order image_type desc,                    image_order              limit 1) pi1                 on products.productid = pi1.productid              products.productid in (966, 967, 968) 

but it's not working , error message:

unknown column 'products.productid' in 'where clause' 

i've worked out error relates products.productid in join derived table, , i've tried various ways result, going round in circles.

can offer suggestion please?

wouldn't work ?

since trying join entire image table , return 1 image , return 1 product directly use left join on product_images , move order by , limit outside of not affect product images.

   select p.productid,            p.name,           p.price,           i.filename,            i.alttag      products p left join (select productid,                   filename,                   alttag              product_images          order image_type desc,                    image_order)        on p.productid = i.productid     p.productid in (1, 2, 3)  group p.productid  order p.price 

live demo.

you can still apply null had:

ifnull(pi1.filename, 'no_image.jpg') image_name,  ifnull(pi1.alttag, 'no information') alt_text