Complicated sql join

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

LVL 1
Black SulfurAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Black SulfurAuthor 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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.