CODEWITHSUNDEEP
sqlsql-server
ulak blade
ulak blade

Reputation: 2665

How can I implement complex flow control in SQL?

Ok so I have a database with these tables:

Products (ID, Producer, Name)
Producers (ID, Name)
ProductTypes (ID, Name)
ProductTypeJunction (ID, ProductID, ProductTypeID)
Transactions (ID, ProductID, Profit)

Basically I have a many-to-many relation between products and product types and each product has a producer. I also keep a list of every transaction made(product sold) and how much money it made.

Now I need to be able to do this:

List the producers who made over 1000 dollars of at least one specific product type, which means it has to at least have made 1000 dollars from a car type or a furniture type. If it has made over 1000 from multiple product type sales combined, it doesn't matter. There have to be 1000 from at least a single type.

There doesn't seem to be any way of achieving this with any SQL functionality that I tried.

Upvotes: 0

Views: 72

Answers (1)

SqlZim
SqlZim

Reputation: 38043

You just need to use group by with having like so:

select
    ProducerName = pro.Name
  , TypeName     = pt.Name
  , Profit       = sum(t.Profit)
from Producers pro
  inner join Products p
    on pro.Id = p.Producer
  inner join ProductTypesJunction ptj
    on ptj.ProductId = p.Id
  inner join ProductTypes pt
    on pt.Id = ptj.ProductTypeId
  inner join Transactions t
    on t.ProductId = p.Id
group by 
    pro.Name
  , pt.Name
having sum(t.Profit)>=1000;

Upvotes: 1

Related Questions