'Count how many records does each user have in the second table

I have 2 Tables (The relevant ones for this question). One for Customers, second for loans.
What I am trying to accomplish is to get each customer's information, and in addition to that how many loans he toked.

My User Table

+--------+-------+-------+-----+
| cst_id | fname | lname | age |
+--------+-------+-------+-----+
|      1 | xx    | yy    |  11 |
|      2 | xx    | yy    |  22 |
|      3 | xx    | yy    |  33 |
|      4 | xx    | yy    |  44 |
|      5 | xx    | yy    |  55 |
+--------+-------+-------+-----+

Loan Table

+---------+-----------------+
| cust_id | loan_variant_id |
+---------+-----------------+
|       1 |               1 |
|       1 |               2 |
|       2 |               3 |
|       2 |               1 |
|       3 |               1 |
+---------+-----------------+

We see that customers with ID 1 have 2 registered loans.

How the final table should look like

you can ignore the new column "name" because it is irrelevant to the question.

+--------+-------+-------+-----+-------+------------+
| cst_id | fname | lname | age | name  | loan_count |
+--------+-------+-------+-----+-------+------------+
|      1 | xx    | yy    |  11 | xx yy |          2 |
|      2 | xx    | yy    |  22 | xx yy |          2 |
|      3 | xx    | yy    |  33 | xx yy |          1 |
|      4 | xx    | yy    |  44 | xx yy |          0 |
|      5 | xx    | yy    |  55 | xx yy |          0 |
+--------+-------+-------+-----+-------+------------+

My SQL code

SELECT
    *,
    CONCAT(fname, ' ', lname) AS name,
    COUNT(##some magic code please, at least what i think##) AS loan_count
FROM
    t_cst

Now I know that via this query I can accomplish what I want (counting How many loans are token by each user) in the t_loans table. However, I am kind of stuck on how to use both queries together.

SELECT
    COUNT(cust_id) AS loansCountForEachCst
FROM
    t_loans
GROUP BY
    (cust_id)

Any Ideas?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source