Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Query/reporting question


Guest

Recommended Posts

Hi,

 

I'm working on an "open orders" report by manufacturer, and have what is probably a simple question for a SQL person...

 

I have created a report that gathers order items, and displays them by manufacturer and item...

 

BUT, I forgot about the attributes.

 

I've added the attributes to the query, but now I only get the items that HAVE attributes... so...

 

Can somebody tell me what I need to do to get BOTH the line items w/o attributes AND the ones with attributes in the same query?

 

Here's the code I have that gets ONLY the ones WITH attribs:

  $orders_query_raw = "SELECT o.orders_id, o.customers_id, o.customers_name, o.date_purchased, o.orders_status, o.orders_date_finished, op.orders_id, op.products_id, op.products_model, op.products_name, op.final_price, op.products_quantity, p.products_id, p.manufacturers_id, m.manufacturers_id, m.manufacturers_name, opa.products_options_values FROM " . TABLE_ORDERS . " o, " . TABLE_PRODUCTS . " p, " . TABLE_ORDERS_PRODUCTS . " op, " . TABLE_ORDERS_PRODUCTS_ATTRIBUTES . " opa, " . TABLE_MANUFACTURERS . " m WHERE o.orders_id = op.orders_id and p.products_id = op.products_id and p.manufacturers_id = m.manufacturers_id and (op.orders_products_id = opa.orders_products_id and op.orders_id = opa.orders_id) and o.orders_status <= 2 ORDER BY m.manufacturers_id,op.products_id,o.date_purchased ASC";

 

Here's the code that gets ALL line items, but doesn't include any attribs:

$orders_query_raw = "SELECT o.orders_id, o.customers_id, o.customers_name, o.date_purchased, o.orders_status, o.orders_date_finished, op.orders_id, op.products_id, op.products_model, op.products_name, op.final_price, op.products_quantity, p.products_id, p.manufacturers_id, m.manufacturers_id, m.manufacturers_name FROM " . TABLE_ORDERS . " o, " . TABLE_PRODUCTS . " p, " . TABLE_ORDERS_PRODUCTS . " op, " . TABLE_MANUFACTURERS . " m WHERE o.orders_id = op.orders_id and p.products_id = op.products_id and p.manufacturers_id = m.manufacturers_id and o.orders_status <= 2 ORDER BY m.manufacturers_id,op.products_id,o.date_purchased ASC";

 

Any help would be GREATLY appreciated.

 

Thanks

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...