Complicated sql join

Black Sulfur
Black Sulfur used Ask the Experts™
on
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)
order_id
product_id
product_qty
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:

voucher_id
product_id
serial (serial number of the voucher/coupon)
rep (sales rep)
purchased (date of purchase)
order_id
customer_id

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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Analyst
Commented:
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

Author

Commented:
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