CODEWITHSUNDEEP
mysqlsumleft-join
sharkyenergy
sharkyenergy

Reputation: 4173

mysql join 2 tables, second table needs SUM()

I have 2 tables,

first contains articles, second contains quantities. the two tables are linked with the "kodex" column. The second table can have multiple records for the same item, but can also have none.

what I need is a query that lists the entire first table, and adds an additional column that contains the sum of all quantities of all entries in the second table.

I have done this with left join on the kodex table, and works fine, but only as long as i do not add the sum() on the select statement. as soon as I do so, it lists only the rows that have a match on the second table.

Query that displays all rows:

SELECT b.* FROM `bestehend` as b left join eingelesen as e on e.kodex=b.kodex

query that displays only rows with matching entries in the second table:

SELECT b.*, sum(e.menge) as gesmenge FROM `bestehend` as b left join eingelesen as e on e.kodex=b.kodex

what I would need is the behaviour of the first query, with the additional column gesmenge from the second query.

thanks!

Upvotes: 1

Views: 422

Answers (1)

KuKeC
KuKeC

Reputation: 4610

Update your query 

SELECT b.*, sum(e.menge) as gesmenge FROM `bestehend` as b left join eingelesen as e on e.kodex=b.kodex

with adding group by b.article_id. Group by is needed so query know how to summarize all quantities of all entries in the second table.

Final query should look like 

SELECT b.*, sum(e.menge) as gesmenge FROM `bestehend` as b left join eingelesen as e on e.kodex=b.kodex group by b.article_id

Upvotes: 1

Related Questions