CODEWITHSUNDEEP
sqlsql-serverjoincount
Stefan Frederiksen
Stefan Frederiksen

Reputation: 135

Count from another table with join

How can I Count the Lending comments for each lending (the comments is on another table called "LendingComments" with a reference Column called "LendingId" ?

SELECT LendingStatus.Status, Products.Productname, Products.Serial_number,    Deposits.Amount, Lendings.DeliveryDate, Lendings.Id AS LendingId, Products.Id AS ProductId  FROM Lendings
LEFT JOIN Products ON Lendings.ProductId = Products.Id
LEFT JOIN LendingStatus ON Lendings.StatusId = LendingStatus.Id
LEFT JOIN Deposits ON Lendings.DepositId = Deposits.Id
WHERE PersonId = 561 ORDER BY DeliveryDate DESC

Upvotes: 0

Views: 108

Answers (3)

Christer Backlund Høllesli
Christer Backlund Høllesli

Reputation: 309

Maby like this (if I understand the question well enough)

SELECT
LendingStatus.Status, Products.Productname, Products.Serial_number,Deposits.Amount, Lendings.DeliveryDate, Lendings.Id AS LendingId, Products.Id AS ProductId, LendingComments.NumLendingComments
FROM Lendings
LEFT JOIN Products ON Lendings.ProductId = Products.Id
LEFT JOIN LendingStatus ON Lendings.StatusId = LendingStatus.Id
LEFT JOIN Deposits ON Lendings.DepositId = Deposits.Id
OUTER APPLY
(
SELECT
    COUNT(*) AS NumLendingComments
    FROM
        LendingComments PL
    WHERE
        PL.LendingID = Lendings.LendingID
) AS LendingComments WHERE Personid = 561 ORDER BY DeliveryDate desc

Upvotes: 1

Devart
Devart

Reputation: 121912

Try this one -

SELECT  ls.status
    ,   p.Productname
    ,   p.Serial_number
    ,   d.AMOUNT
    ,   l.DeliveryDate
    ,   l.Id AS LendingId
    ,   p.Id AS ProductId
    ,   pl.cnt
FROM dbo.Lendings l
LEFT JOIN (
    SELECT pl.LendingId, cnt = COUNT(pl.LendingComments)
    FROM dbo.PersonLendings pl
    GROUP BY pl.LendingId
) pl ON pl.LendingId = l.LendingId
LEFT JOIN dbo.Products p ON l.ProductId = p.Id
LEFT JOIN dbo.LendingStatus ls ON l.StatusId = ls.Id
LEFT JOIN dbo.Deposits d ON l.DepositId = d.Id
WHERE PersonID = 561
ORDER BY l.DeliveryDate DESC

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460058

Maybe this helps:

SELECT CommentCount = Sum(lc.comments) 
                      OVER ( 
                        partition BY lc.id), 
       lendingstatus.status, 
       products.productname, 
       products.serial_number, 
       deposits.amount, 
       lendings.deliverydate, 
       lendings.id AS LendingId, 
       products.id AS ProductId 
FROM   lendings 
       LEFT JOIN products 
              ON lendings.productid = products.id 
       LEFT JOIN lendingstatus 
              ON lendings.statusid = lendingstatus.id 
       LEFT JOIN deposits 
              ON lendings.depositid = deposits.id 
       LEFT JOIN LendingComments lc
              ON lc.LendingId = lendings.Lendings.Id
WHERE  personid = 561 
ORDER  BY deliverydate DESC

However, you have not shown the PersonLendings table, have you?

Upvotes: 0

Related Questions