A complicated query?
Posted by: Grace Xue
Date: May 28, 2014 05:17AM
Hi all, I'm really new to programming, so appologise in advance if any of my questions seems silly. :) I'm playing with an webapp for internal use, however having trouble completing a query. below are the table structure.
ID, LID, Network_id, status (active or archived), longitude, latitude
ID, visit_id, child_id, cancelled
Each children get visited once a month, and I want to have a google map that can display the locations for all active children whose last visit is xx days ago. (e.g. 30 days ago)
I'm thinking creating a view that returns the followings:
child_id, network_id, last_visit_days, longitude, latitude
- Some children who just joined the program, so there wouldn't be a record in the visit_histories table. the returned value in this case can be nil.
- Some booked visits are cancelled because of one reason or another.
- the last_visit_days in the view is a calculated field using the last_visit_day in the visit_histories (only the ones that havnt been cancelled)
I have this query
Select vh.child_id, max(v.date) from visits v, visit_histories vh where v.id = vh.visit_id and cancelled <> 1 group by vh.child_id
which displays the last visit date for each child in the visit histories table. But this does not display new child who just joined. I then need to combine this result with the children table to get all the children, however I cant get the outter jion query to work properly.
Many thanks in advance.