CODEWITHSUNDEEP
mysqlsql
Roman Roman
Roman Roman

Reputation: 917

How to count rows in nested tables with one SQL query?

I have three tables. Each User can have multiple Subscriptions and each Subscription can have multiple Payments.

Me goal is to count all Payments for a single User using one SQL query. Is it possible to do and how?

In the case below, The result for a User with id 1 should be 2 (because the User has two Payments)

Users
+----+------+
| Id | Name |
+----+------+
| 1  | John |
+----+------+

Subscriptions
+----+--------+-----------+
| Id | userId |   data    |
+----+--------+-----------+
| 1  |   1    | some data |
+----+--------+-----------+
| 2  |   1    | some data |
+----+--------+-----------+

Payments

+----+----------------+--------+
| Id | subscriptionId | amount |
+----+----------------+--------+
| 1  |        1       |   30   |
+----+----------------+--------+
|  2 |        2       |   50   |
+----+----------------+--------+

Upvotes: 1

Views: 1130

Answers (2)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

try like below by using join and aggregation 

SELECT u.id, u.Name, COUNT(p.id) AS numberofpayment
FROM users u
  Left JOIN Subscriptions s ON u.Id=s.userId
  Left JOIN Payments p ON s.id=p.subscriptionId
GROUP BY u.id, u.Name

Upvotes: 3

cn0047
cn0047

Reputation: 17061

You can try to do something like this:

SELECT COUNT(p.Id) AS PaymentCount
FROM Users u
  LEFT JOIN Subscriptions s ON u.Id=s.userId
  LEFT JOIN Payments p ON s.id=p.subscriptionId
WHERE u.Id = @yourUserID

Pay attention on COUNT(p.Id) - it means count of existing payments.

PS: this answer for @Kickstart.

Upvotes: 1

Related Questions