Hello, I have a query that I can't figure out how to do. I would be really
happy if some SQL experts could give me a little advice on how to do this....
I have two tables. I believe that the tables can be described as having a
one to many relationship. Below are the columns in each table, relevant to
the query...
Table: assessments
---------------
assessment_id int() primary key
sent_status tinyint()
Table: assessment_hits
---------------
assessment_id int()
hit_date timestamp(14)
The assessment_id column in assessment_hits doesn't have to be unique, so the
table has many rows with the same assessment_id and different hit_date values.
What I want to find out is, how many assessments.assessments_ids with a
sent_status =1 there are for each month.
Here's what I did that didn't work...
SELECT DATE_FORMAT(h.hit_date, '%Y %M') as month, count(*) as total
FROM assessment_hits h, assessments a
WHERE a.assessment_id = h.assessment_id
AND a.sent_status = 1
GROUP BY LEFT(h.hit_date, 6)
This gives me how many assessment_ids with a sent status = 1 there are in the
assessment_hits for each month.
How do I just use one hit_date from assessment_hits for each
assessments.assessment_id with a sent_status=1? I don't care which hit_date
it uses, preferably the most recent one, but it doesn't matter.
I'm thinking that I have to use a special join, but I don't know.
Thanks,
Nathanial Hendler
Tucson AZ USA
http://retards.org/
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
