Merging 3 MySQL Tables Into Consolidated Table Getting Too Many Results

Question

I have 3 tables: members, products, product_costs

I need to consolidate the info from these tables into one single table. When I do this, I'm getting far too many results and I'm not sure why.

For every product, there should be a cost, but there might not be. In the event there is a product, but no cost, I want it to throw nulls or zero's into those columns.

Here are the table structures:

Members Table

member_id       first_name      last_name

Products Table

product_id      member_id       product_date

Product Costs Table

product_cost_id     member_id       product_cost_amount

Here's what I'm looking for in the consolidated table:

Consolidated Table Structure

member_id       first_name      last_name       product_id      product_date        product_cost_amount

To get there I wrote the following query:

    SELECT
        M.member_id,
        M.first_name,
        M.last_name,
        P.product_id,
        P.product_date,
        PC.product_cost_amount
    FROM
        members AS M
        INNER JOIN products AS P ON P.member_id = M.member_id
        INNER JOIN product_costs AS PC ON PC.member_id = M.member_id

This will return all of the columns that I want, which is not the problem. Say I have 1 record for a member in the members table and I have 5 products for that member, which also has 5 costs. When I run this query, I get 25 results and I'm not sure why. I expect a total of 5 results, 1 line per member selected product with the member details and the cost thrown in. What I get is 1 line per product with 5 lines of cost for the same product repeated.

The problem seems to be INNER JOIN product_costs AS PC ON PC.member_id = M.member_id, but I've tried running that with different join types and I'm still not seeing the correct results and without that line how do I connect the costs since there's no direct relationship? I've sketched diagrams of this on paper and I'm still not understanding what I'm doing wrong. I've built a smaller test database to simplify and tried it again with 100% clean data and still the same results (the actual database this is coming from is huge and the query is massive so I've simplified it here to isolate the result and make it easier to work with).

Can anybody please help me out to understand why this join doesn't work? Could it be that there's no relationship directly between the cost and product itself? If so, I'm not opposed to direct linking the two since technically there should ALWAYS be a cost, even if that cost is technically free. I'm just inheriting this database design and given that they're still tied to the member through the member_id, I'm not understanding why this is happening.

Thank you in advance for any help you can offer!

Answer 1

You are missing a join condition on product_costs to the products table:

FROM members M INNER JOIN
     products P
     ON P.member_id = M.member_id INNER JOIN
     product_costs PC
     ON PC.member_id = M.member_id AND
        PC.product_id = P.product_id