How to output data from two SQL tables?-CodePudding

I need to find clients whose personal account balance is more than 100. Output client_id,balance, find clients who have more than 3 sim cards. Output name, number of sim cards, find customers whose birthday is next month. Output name, birth_date. I do not understand how these tasks can be done, I have tried many options. I am very grateful for the help

CodePudding user response：

Here is one way:

select 
    c.*
from
    clients c
    left join accounts a on a.client_id = c.client_id
    left join 
        (select account_id, count(*) cnt from sim_cards group by account_id) s 
            on s.account_id = a.account_id
where
    s.cnt > 3
    and a.balance > 100

CodePudding user response：

And finding the customer whose birthday is in next month can be done in several ways. Here are two:

SELECT
*
FROM CLIENTS
WHERE MONTH(birth_date) == 11 -- here you have to specifiy the month by yourself

SELECT
*
FROM CLIENTS
WHERE MONTH(birth_date) = MONTH(DATEADD(month, 1, GETDATE()))