MySQL Forums
Forum List  »  Newbie

Complex query to compare values
Posted by: rens korver
Date: February 24, 2010 09:06PM

Hello all
I'm running mysql 5.1
I need a query for my database. I'm a real newbie at SQL. I can do some basic select statements but I need something more complex.
I have 2 tables:
1) resident
2) weight

Resident has the following columns:
Resident_ID primary key and auto_incrament
Surname
First_Name
Upper_weight_limit
Lower_weight_limit
Active (check box. Value returned is 1 or 0)

Weight has the following columns:
Weight_ID primary key and auto_increment
Resident_ID
date
weight
Action_taken

Resident_ID field would be the JOIN field for the two tables.

End users of the database enter weights for residents every 2-6 weeks.
A second end user needs to be alerted if the weight meets certain criteria (the query that I need)so they can complete the Action_take field.
I require a query that will list for me the Surname, First_Name and a third column (described below)of all residents where Active=1 and Action_taken is Null who's last recorded weight is either above the Upper_weight_limit, Below the Lower_Weight Limit or who have lost more than 10% of their weight in the last 6 months (keeping in mind weights are entered at random dates every 2-6 weeks).
As stated the query needs to return the resident's Surname, First_Name and a third column. The third column should tell the end user what criteria the result matches. For example:
Smith Joan weight>upper_weight_limit
Jones Frank weight loss > 10% over 6 months

I hope I have explained myself well.
Thanks in advance for all your help.
Regards
Rens

Thanks.



Edited 1 time(s). Last edit at 02/24/2010 09:25PM by rens korver.

Options: ReplyQuote


Subject
Written By
Posted
Complex query to compare values
February 24, 2010 09:06PM


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.