Skip to Content
Sep 20, 2019 at 03:15 PM

Duplicate Results When Creating Formulas Based on Joined 2 tables


My group on " itemnum" works in a formula I created until I join with another table on itemnum....then when i create and pull in a second formula based on the latter table then the results in the first formula show duplicates, triplicates and sometimes even quintiplicates!

Here's my show sql query: SELECT DISTINCT `orderlines1`.`itemnum`, `items1`.`class`, `items1`.`description`, `items1`.`buyer`, `items1`.`itemnum`, `orderlines1`.`qtyordered`, `orderlines1`.`qtyinvoiced`, `orders1`.`ordertype`, `orderlines1`.`ordernum`, `openpo1`.`poe`, `openpo1`.`qtybo` FROM ((`tov`.`items` `items1` LEFT OUTER JOIN `tov`.`orderlines` `orderlines1` ON `items1`.`itemnum`=`orderlines1`.`itemnum`) LEFT OUTER JOIN `tov`.`openpo` `openpo1` ON `items1`.`itemnum`=`openpo1`.`itemnum`) INNER JOIN `tov`.`orders` `orders1` ON `orderlines1`.`ordernum`=`orders1`.`ordernum` WHERE `orderlines1`.`qtyordered`<>`orderlines1`.`qtyinvoiced` AND `items1`.`itemnum`='20282' ORDER BY `items1`.`itemnum`, `orderlines1`.`ordernum`

Note: Source of duplication seems to be duplicating the same "ordernum" twice

Please help.