Skip to content

Cannot use left join when trying to add new table to update. #65

@brittainmark

Description

@brittainmark

Trying to add product location to easypolulate. Using code you supplied.
The issue I have is it does not like the left join.

MySQLi error 1054: Unknown column 'p.products_id' in 'on clause'
When executing:
SELECT    
          p.products_id         as v_products_id,
          p.products_type         as v_products_type,
          p.products_model        as v_products_model,
          p.products_image        as v_products_image,
          p.products_price        as v_products_price,
          pl.vs_products_location as v_vs_products_location,p.products_qty_box_status as v_products_qty_box_status,p.products_quantity_order_max as v_products_quantity_order_max,p.products_weight      as v_products_weight,
          p.products_discount_type    as v_products_discount_type,
          p.products_discount_type_from   as v_products_discount_type_from,
          p.product_is_call       as v_product_is_call,
          p.products_sort_order     as v_products_sort_order,
          p.products_quantity_order_min as v_products_quantity_order_min,
          p.products_quantity_order_units as v_products_quantity_order_units,
          p.products_priced_by_attribute  as v_products_priced_by_attribute,
          p.product_is_always_free_shipping as v_product_is_always_free_shipping,
          p.products_date_added     as v_date_added,
          p.products_date_available   as v_date_avail,
          p.products_tax_class_id     as v_tax_class_id,
          p.products_quantity       as v_products_quantity,
          p.products_status       as v_products_status,
          p.manufacturers_id        as v_manufacturers_id,
          p.metatags_products_name_status as v_metatags_products_name_status,
          p.metatags_title_status     as v_metatags_title_status,
          p.metatags_model_status     as v_metatags_model_status,
          p.metatags_price_status     as v_metatags_price_status,
          p.metatags_title_tagline_status as v_metatags_title_tagline_status,
          subc.categories_id        as v_categories_id
          FROM products as p, products_to_categories as ptoc,categories as subc  LEFT JOIN very_simple_products_location AS pl ON (pl.products_id = p.products_id) WHERE
          p.products_id      = ptoc.products_id AND
          ptoc.categories_id = subc.categories_id AND 
            p.products_model   = '21TR008P'

I have played around with the SQL and the issue appears to be that the left join is trying to join to ptoc.categories.

If I move the join

FROM products as p LEFT JOIN very_simple_products_location AS pl ON (pl.products_id = p.products_id) , 
products_to_categories as ptoc, categories as subc  

Works fine.

It also works if all joins are used

FROM products AS p INNER JOIN products_to_categories AS ptoc ON p.products_id = ptoc.products_id 
INNER JOIN categories AS subc  ON ptoc.categories_id = subc.categories_id 
LEFT JOIN very_simple_products_location AS pl ON (pl.products_id = p.products_id) 
WHERE p.products_model = '21TR008P'

I was wondering If you would like me to go through and rewrite all the selects to use JOIN and submit as a PR?

PHP Version 8.0
MYSQL Ver 8.0.29-0ubuntu0.20.04.3
Zen cart 1.5.8.

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions