COUNTING records in 2nd table

[murraythedog]

My system allows users to login using USER and PWD. Whilst authenticating them I'd like to tell them how many messages they have received since last login. I can do this inefficiently (ie with two queries) but I can't figure out a single query to provide the info.

The two tables are like:
users: idnum, username, password, lastlogin as date
messages: msgnum, subject, body, date_received as date

The query which ALMOST works is:
select idnum, lastlogin, count(msgnum) FROM users, messages WHERE username='USER' AND password=md5('PWD')
AND (date_received > lastlogin) GROUP BY msgnum

...but this clearly fails to authorise someone who has no new messages, so that won't do. Any ideas, folks?

TIA

[ChadSEO]

murraythedog,

Welcome to WebmasterWorld!

Something like this should work:

SELECT idnum, lastlogin, count(msgnum)
FROM users left outer join messages on users.lastlogin < messages.date_received
WHERE username='USER' AND password=md5('PWD')
GROUP BY msgnum

By doing an outer join, it will still return results even if there are no records that can be joined. I didn't test that query, so you might have to tweak it a bit, but hopefully it'll set you on the right track.

Chad

[murraythedog]

Yup, that did it. Seems as good a way to get to grips with outer joins as any (in fact I might be able to make sense of them now I have a need for one!).

Many thanks for your help.