Bad Addy Bad Addy - 1 year ago 86
MySQL Question

mySQL Not Working as expected

Basic example is this:

3 tables:

Orders, holds only the OID I need to link with Cart;

Products, holds the product Name, and SKU I need;

Cart, holds the OID and product Name.

It is an old system, but I am trying to create a report where the User enters the SKU to get all orders that relate to it:

SELECT * FROM orders o
LEFT JOIN cart c ON ( = c.session_id)
WHERE product_name =
(SELECT product_sku FROM products WHERE product_code = c.prorduct_name)
ORDER by sdate DESC;

This does not work, I have tried to add the join cart in with sub select, but that did not work either. They give me either no results, or an error with the column being found.

Have I given enough information for this to make sense ? And any ideas what I am doing wrong ?


SELECT * FROM orders o
LEFT JOIN cart c ON ( = c.session_id)
LEFT JOIN products p ON (p.product_code = c.product_name)
WHERE {$sku} o.senddate BETWEEN '{$_REQUEST["date3"]}' AND
'{$_REQUEST["date4"]}' ORDER BY o.senddate ASC

I managed to achieve the result using workbench and basically putting the code in a different order. This seemed to do the trick :)

Answer Source

You have a small typo in your query you write c.prorduct_name should it be c.product_name. Use INNER JOIN instead of LEFT JOIN

Additionally if i understand the schema of your tables correctly you can re-write your query like this.

SELECT * FROM orders AS o,
cart AS c,
products AS p
WHERE c.product_name = p.product_code
AND = c.session_id
AND product_sku = 'the_selected_SKU'
ORDER by sdate DESC;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download