CODEWITHSUNDEEP
.netsql
SNA
SNA

Reputation: 7728

Sql ,query for getting total count joining 2 tables

I am new to sql.

Here is my question

The requirement is to join 2 tables and get the total count

Order: {ID, AccountID, OrderName}

OrderDetails: {ID, OrderID, Productcode, ProductTitle, ...}

Now I want to get the total count of orders which has associated 'Orderdetails' for a given account.

More Info..

Join these two tables on OrderID for a particular AccountId and get total count.

also remove duplicates. that means order id repeats in the second table for product codes.

There are instances order is inserted without order details.That means OrderID not present in OrderDetails.

My query'

select count(*) from(
    SELECT MO.id
    FROM dbo.Order MO
    JOIN
        dbo.OrderDetails MOD ON 
        MO.ID = MOD.OrderID
        WHERE MO.AccountID ='123rt65-eee-ddd-ddd-dddddd'
        group by MO.ID) n

this gives me the correct count.

Is this the right way? or is there any better way to achieve this?

Thanks

SNA

Upvotes: 1

Views: 460

Answers (1)

roman
roman

Reputation: 117520

you can do this without subquery:

select count(distinct MO.ID)
from dbo.Order as MO
    inner join dbo.OrderDetails as MOD on MOD.OrderID = MO.ID
where MO.AccountID ='123rt65-eee-ddd-ddd-dddddd'

or, as you don't need data from OrdersDetails:

select count(*)
from dbo.Order as MO
where
    MO.AccountID ='123rt65-eee-ddd-ddd-dddddd' and
    exists (select * from dbo.OrderDetails as MOD where MOD.OrderID = MO.ID)

BTW, it's better to not call your table Order, because this is reserved keyword for order by.

Upvotes: 1

Related Questions