CODEWITHSUNDEEP
mysql
Daniela Callisaya P.
Daniela Callisaya P.

Reputation: 3

Select based on value of another select

I have two tables that like this:

'BUDGET TABLE' 

idBudget BudgetDescription  CreateDate  idProject
   1       NameBudget1      09/09/2015     2
   2       NameBudget2      08/07/2015     1
   3       NameBudget3      08/09/2015     1

'ITEMS' the items in every budget

idItem  ItemDescription  Price IdBudget
  1      Item1             10     1
  2      Item2             30     1
  3      Item3             5      2
  4      Item4             130    3
  5      Item5             27     3

And I'm trying to get these kind of results if the USER wants to see the budgets that belong to Project = '1'

-----------------------------------------------
BudgetDescription    CreateDate    Sum(Price) as Total 
----------------------------------------------
  NameBudget2        08/07/2015      5
  NameBudget3        08/09/2015     157
-----------------------------------------------

I tried to join these querys:

Select idBudget, BudgetDescripcion, CreateDate
from budget
where idProject='1';

Select sum(price) as total
from Items i
where i.idBudget=idBudget;

like

Select sum(price) from item where idBudget=(select idBudget, BudgetDescription, createdate from Budget where idProject='1');

but thats gives me an error: "Operand shoul contain 1 column"

so I tried and erase the other columns:

Select sum(price) from item where idBudget=(select idBudget from Budget where idProject='1');

and error: Subquery returns more than 1 row

Thank you very much for your help.

Upvotes: 0

Views: 77

Answers (2)

Tomalak
Tomalak

Reputation: 338128

select 
  b.idBudget, 
  b.BudgetDescripcion,
  b.CreateDate BudgetCreateDate,
  COUNT(i.idItem) CountItems,
  SUM(i.price) SumItemPrice
from 
  budget b
  inner join Items i on i.IdBudget = b.idBudget
where
  b.idProject = 1
group by
  b.idBudget, b.BudgetDescripcion, b.CreateDate;

Upvotes: 1

Bernd Buffen
Bernd Buffen

Reputation: 15057

LEFT JOIN is what you seek.

Select b.idBudget, b.BudgetDescripcion, b.CreateDate, sum(i.price) as total from budget b LEFT JOIN Items i ON i.idBudget=b.idBudget where b.idProject='1';

Upvotes: 0

Related Questions