CODEWITHSUNDEEP
mysql
Ali Ansari
Ali Ansari

Reputation: 152

selecting from two tables with a condition which effect only on one table

I have two tables clients and transactions and I need to take a query from these two tables in a way which all the clients should select with the total of their transactions.
My problem is when I do a query from these two tables and set the condition; which transactions should have the clients id it shows only those clients that have record in transaction table, but I want it display all the clients even if they don't have any transaction(it can display zero instead sum of transaction).
I know because of condition which belongs to transaction table, query doesn't select persons in clients table which doesn't meet the query requirement, but how can I select all the clients and sum of their transactions or put zero if they don't has any transaction.

this is a short view of tables (only those columns I used in query)

ID   Name    Company    Phone  //clients table

ID   Client_id    Incoming    ...   //transaction table

Thank you in advance and sorry for my bad english

Upvotes: 0

Views: 79

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270763

In addition, you can also do this with a correlated subquery:

SELECT c.*,
       (select sum(t.incoming) - sum(t.outgoing)
        from transactions t
        where t.client_id = c.id
       ) as total
from clients c;

Under some circumstances, this could have better performance.

Upvotes: 1

kyoun
kyoun

Reputation: 56

you could use a left join, something like : SELECT * FROM clients LEFT JOIN transaction ON client.id = transaction.Client_id

You would get all clients, empty rows from transaction would be set to null, so you'll have to change that to 0

Upvotes: 1

abrcek
abrcek

Reputation: 93

SELECT c.Name, count(t.ID) 
FROM clients c 
left join transactions t on c.CustomerID = t.Client_id
group by t.client_id

Upvotes: 1

Related Questions