CODEWITHSUNDEEP
mysqlsqldatabase
Moose
Moose

Reputation: 1241

Save Result of SQL Statement to Use Elsewhere

I'm trying to figure out a way to write an sql query for the number of files that have been sent since 2014-07-01 (the following example should result in 4). I have two different tables that I need to query against because the date is only available in the first one. They are as follows:

messages:

________________________________
messageid | Message | datesent |
1           xxxx      2014-06-30
2           xxxx      2014-07-01
3           xxxx      2014-07-03

files:

________________________________
filename        |   messageid
red.txt             1
blue.txt            1
green.txt           1
fish.pdf            2
google.doc          3
bing.doc            3
duckduckgo.doc      3

So I have a one to many relationship seeing as one message can have more than one file attached to it.

So far I have used the following to get the messageid of the first message since 2014-07-01.
SELECT (messageid) FROM messages WHERE (datesent) = '2014-07-01' LIMIT 1
I suspect I need to use this as a starting point, but I don't know where to go from here. Any help would be greatly appreciated!

Upvotes: 0

Views: 60

Answers (2)

pid
pid

Reputation: 11607

Use this:

SELECT COUNT(*)
FROM messages INNER JOIN files ON messages.messageid = files.messageid
WHERE datesent >= '2014-07-01';

Upvotes: 1

Andreas
Andreas

Reputation: 5103

Something like this?

select count(0)
from messages as m
join files as f on f.messageid=m.messageid
where m.datesent>'2014-07-01'

Upvotes: 2

Related Questions