SQL Query :Filter help needed

Question

I have 2 tables.One is OrderMaster and second is OrderDetails.Both are connected via OrderId

Order table fields : OrderId(Primary Key),Total,OrderDate

OrderDetail fields : OrderDetailId,ItemId,SupplierId,Amount,OrderId (ForiegnKey)

One order can have multiple orderdetail records which can be from various suppliers

Now i want to get Orders with only a purticular SupplierId value(Ex : 4) and the count (distinct supplierid)=2 .All Orders which have this supplier Id should not be included in the result set as some of it may having other suppliers too.The expected output is OrderId, Sum*Amount for those records which belongs to a particular supplier (Only this supplier in the order)

EDIT : i think Count(supplierId)=1 should be a part of the query.

Answer 1

Try something like this:

select OrderId, Sum(Amount) 
from OrderDetail 
where SupplierId = 4 
group by OrderId

Answer 2

You can try something like this.

DECLARE @Supl INT

SELECT @Supl = 4

SELECT  om.OrderID,
     SUM(Amount) TotalForSupplier
FROm    OrderMaster om INNER JOIN
     OrderDetails od ON om.OrderID = od.OrderID
WHERE   od.SupplierId = @Supl
GROUP BY om.OrderID

This will get you the totals for a given supplier accross orders.

This should probable include a price field,

SUM(Amount * Price)

If you wish to only have the OrderID and Total you can exclude the inner join

SELECT  od.OrderID,
    SUM(od.Amount) TotalForSupplier
FROm    OrderDetails od
WHERE   od.SupplierId = @Supl
GROUP BY od.OrderID

Answer 3

Here's one way to select orders with only one particular supplier. The where clause looks for details for that supplier, and filters out orders with mixed suppliers. The Total is then calculated for each detail row.

select     od.OrderId
,          sum(od.Sum * od.Amount) as Total
from       OrderDetails od
where      od.SupplierId = 4
and        not exists (
           select   *
           from     OrderDetails od2
           where    od2.OrderId = od.OrderId
           and      od2.SupplierId <> od.SupplierId
           )
group by   od.OrderId

I'm not sure the sum is correct; here it's multiplying the Sum column with the Amount column. If you just need amount, you could write it like:

,          sum(od.Amount) as Total

Joining on OrderMaster is not required, since we can grab the OrderId from the details table.