Count from another table with join

Question

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

Christer Backlund Høllesli · Accepted Answer · 2013-04-22 11:07:49Z

1

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

answered Apr 22, 2013 at 11:07

Christer Backlund Høllesli

3091 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Christer Backlund Høllesli Over a year ago

No problem, glad I could help! Use the "thumbs up" when and if you can :)

Tim Schmelter · Accepted Answer · 2013-04-22 11:00:56Z

0

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?

answered Apr 22, 2013 at 11:00

Tim Schmelter

462k79 gold badges720 silver badges981 bronze badges

1 Comment

Stefan Frederiksen Over a year ago

Sorry the "PersonLendings" is called LendingComments

Devart · Accepted Answer · 2013-04-22 11:07:28Z

0

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

answered Apr 22, 2013 at 11:07

Devart

120k23 gold badges172 silver badges189 bronze badges

Collectives™ on Stack Overflow

Count from another table with join

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related