Complicated sql join

Black Sulfur
Black Sulfur used Ask the Experts™
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
        LIMIT 10
        $this->db->bind("rep", $_SESSION['rep_id']);
        $results = $this->db->resultSet();
        return $results;

Open in new window

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Analyst
Your statement uses more tables than you post in description but regardless of that one thing is that when you join with order_detail you must use both order_i and product_id right? something like:

INNER JOIN order_detail AS od ON od.order_id = v.order_id and od.product_id = v.product_id

Also not sure why you need to
INNER JOIN `order_summary` AS `os` ON os.`order_id` = v.`order_id`

if you don't return anything from it or you don't filter data based on some columns from it


Thank you, that seems a lot better. I will just check my results in more detail but thanks again, it looks a lot better so far.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial