MySQL - Sum on many to many relation

Question

I have the following two tables:

ORDERS_ENTRIES

orders_id | products_id | entry_id | quantity
1111 | 14492 | 1 | 1
1112 | 14492 | 1 | 3

And

PRODUCT_STOCKS

products_id | entry_id | quantity
14492 | 1 | 4
14492 | 1 | 2

I am trying to find out the SUM(ORDERS_ENTRIES.quantity) as well as the quantity from products_stocks via the following query:

SELECT 
    oe.entry_id as entry,
    SUM(oe.quantity) as qty,
    ps.quantity as ps_qty
FROM
    ORDERS_ENTRIES oe
    INNER JOIN
        PRODUCT_STOCKS ps
            ON 
                oe.entry_id = ps.entry_id;
WHERE
    oe.products_id = 14492;
GROUP BY
    ps.entry_id;

But the result returned counts the amount multiple times due to the many to many relationship.

Expected Answer:

oe.entry_id | oe.quantity | ps.quantity
1 | 4 | 4
1 | 4 | 2

But I get the following answer:

oe.entry_id | oe.quantity | ps.quantity
1 | 8 | 4
1 | 8 | 2

How can I do this?

Answer 1

This is because there are multiple matching records are in joining tables. You can avoid duplicate with group by in a sub query

SELECT 
    oe.entry_id as entry,
    oe.qty,
    ps.quantity as ps_qty
FROM
  (SELECT entry_id, SUM(quantity) as qty  FROM ORDERS_ENTRIES GROUP BY  entry_id) AS oe
    INNER JOIN
        PRODUCT_STOCKS ps
            ON 
                oe.entry_id = ps.entry_id;
WHERE
    oe.products_id = 14492;
GROUP BY
    ps.entry_id;

Answer 2

By observation, it appears that you want to first aggregate the quantity in the ORDERS_ENTRIES table by entry_id, and then join this result to the PRODUCT_STOCKS table:

SELECT oe.entry_id,
       oe.qty,
       ps.quantity AS ps_qty
FROM
(
    SELECT entry_id, SUM(quantity) AS qty
    FROM ORDERS_ENTRIES
    GROUP BY entry_id
) oe
INNER JOIN PRODUCT_STOCKS ps
    ON oe.entry_id = ps.entry_id

Output:

Demo here:

Rextester