SQL Query to list details by Age

Hi All,

I have a Tenant table and want the query to list all tenants in Age bracket between age 14 and 24. But since we have the mobile no of the main tenant we also want to show their record as well. In certain cases the main tenant can also be in the age group 14 -24 and should include these tenants as well.

Below is how the view should look like. I have uploaded sample data here: https://app.box.com/s/ky3ry6x5zk587j7nvuk76dg0ezmsv3kn

Tenant   Code Title Full Name Age Member Type Relationship to   Head Mobile
42447      Ms         Wendy 50 Main Tenant NA   123231
42447     Miss       Jamie 14 Member DAU   
42447     Miss       Kristen 18 Member DAU   

Thanks


February 15th, 2015 5:31am

Hi

Can you please provide the result that you want get according to your sample data?
It will clear what you are ask for.

* if you return all tenants in Age bracket between age 14 and 24 then you already returned main tenant in the age group 14 -24, since he is tenant

Free Windows Admin Tool Kit Click here and download it now
February 15th, 2015 5:59am

The best way to provide sample data is to post CREATE TABLE statements and INSERT statements with sample data, as that makes it easy to copy and paste into a query window to develop a tested query. Thus, the below is an untested query:

SELECT ...
FROM   tenants
WHERE  age BETWEEN 14 AND 14
UNION
SELECT ...
FROM   tenants a
WHERE  EXISTS (SELECT *
               FROM   tenants b
               WHERE  a.tenant_code = b.tenant_code
                 AND  b.age BETWEEN 14 AND 24)

February 15th, 2015 6:26am

What means 'main' tenant?If you want show the details like a mobile number so just include this in SELECT statement, am I missing something?

SELECT<columns> FROM tbl WHERE age>=14 and age<=24

Free Windows Admin Tool Kit Click here and download it now
February 15th, 2015 6:28am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics