My title is that it's complicated. Complicated for me that is :-P
When a product is purchased it goes into a product summary and product detail table. The product detail table holds info like the order id and then each item that was ordered as well as the qty.
Here is the basic structure
order_detail_id (primary key)
product_price (this is the price of the item at the time of purchase)
Then at the same time, data is inserted into a coupon/voucher table but that table holds no item price.
I am trying to join the 2 tables but having difficulty in getting the product price.
The voucher/coupon table:
serial (serial number of the voucher/coupon)
rep (sales rep)
purchased (date of purchase)
I want to show each voucher serial number with the price it was sold for. But I can't seem to get it right. The prices don't match up with the serial or it shows duplicate serials.
$this->db->query("SELECT `serial`, `purchased`, `vcs_code`, `prod_name` FROM `vouchers` AS `v`
INNER JOIN `order_summary` AS `os` ON os.`order_id` = v.`order_id`
INNER JOIN `bb_products` AS `bbp` ON bbp.`prod_id` = v.`product_id`
INNER JOIN `order_detail` AS `od` ON od.`order_id` = v.`order_id`
WHERE `rep` = :rep
ORDER BY `purchased` DESC
$results = $this->db->resultSet();