A complicated query?

[Grace Xue]

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.

Children
ID, LID, Network_id, status (active or archived), longitude, latitude

Visit
ID, date

Visit_histories
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

Other notes:
- 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.