MySQL Forums
Forum List  »  Ruby

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.

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.

Options: ReplyQuote


Subject
Written By
Posted
A complicated query?
May 28, 2014 05:17AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.