Zoitc2014 Zoitc2014 - 2 months ago 5
MySQL Question

How to join my "clients" and "messages" tables?

There are two tables:

Clients
___________
idClient int
login varchar

Messages
___________
idMessage int
dateWakeup datetime
.... other fields


I need for each client to count the number of entries in the table Messages in a given range of time. I tried something like this:

SELECT c.login, count(m.idMessage) FROM Clients c, Messages m
where
m.idClient=c.idClient and m.dateWakeup>'2010-09-01 00:00:01'
and m.dateWakeup<'2010-10-01 00:00:01';


It is not working.

Answer

When using COUNT(...), you have to GROUP BY your results:

SELECT c.login, count(m.idMessage)
FROM Clients c, Messages m
where m.idClient=c.idClient
and m.dateWakeup>'2010-09-01 00:00:01'
and m.dateWakeup<'2010-10-01 00:00:01'
GROUP BY c.login
Comments