selecting 2 records results in duplicate content

Forum Moderators: open

Message Too Old, No Replies

selecting 2 records results in duplicate content

jake66

3:00 am on Jan 13, 2008 (gmt 0)

My query:

$this->queryItemCnt = "SELECT o.orders_id, op.products_id as pid, op.products_model as modelid, op.orders_products_id, op.products_name as pname, sum(op.products_quantity) as pquant, sum(op.final_price * op.products_quantity) as psum, op.products_tax as ptax FROM " . TABLE_ORDERS . " o, " . TABLE_ORDERS_PRODUCTS . " op WHERE o.orders_id = op.orders_id and op. products_stock_detail = '0' and op. manufacturers2_id = '21' or op. manufacturers2_id = '22'";

The part I bolded is what's causing duplicate content. Basically what happens is; when I remove that, the script works perfectly.

If I try to add it back, everything is multiplied by 2.

ZydoSEO

3:48 am on Jan 13, 2008 (gmt 0)

Without knowing your schema, it's hard to say. It could be an AND/OR operator precedence thing. Try modifying your WHERE clause to either:

a) surround your OR clause in parens as in:

WHERE o.orders_id = op.orders_id and op. products_stock_detail = '0' and (op. manufacturers2_id = '21' or op. manufacturers2_id = '22')";

b) convert to using IN as in:

WHERE o.orders_id = op.orders_id and op. products_stock_detail = '0' and op. manufacturers2_id IN ('21', '22')";

If that doesn't work please provide a little more info about how the TABLE_ORDERS and TABLE_ORDERS_PRODUCTS tables are related (foreign keys, main fields, etc.)

jake66

4:01 am on Jan 13, 2008 (gmt 0)

a) surround your OR clause in parens as in:
WHERE o.orders_id = op.orders_id and op. products_stock_detail = '0' and (op. manufacturers2_id = '21' or op. manufacturers2_id = '22')";

Worked! Thanks so much for the help!

ZydoSEO

5:16 pm on Jan 13, 2008 (gmt 0)

BTW... Both option a) and b) above are logically equivalent. When I have several OR clauses (A=B OR A=C OR A=D...) I tend to use the IN operator because it is very consise and make my WHERE clause much smaller and easier to understand... It removes any ambiguity related to logical operator precedence.

NOT IN on the other hand I typically avoid.

Glad it worked.