Requires Free Membership to View
Assuming you have tables similar to this:
create table Person ( Name varchar(10) primary key, Age int not null ); create table AgeFactor ( Age int primary key, Factor varchar(20) not null );
With data similar to this:
insert into Person values ('John B' , 37);
insert into Person values ('John J' , 2);
insert into Person values ('Jimmy P' , 65);
insert into Person values ('Robert P', 17);
insert into Person values ('Eric C' , 84);
insert into Person values ('Syd B' , 42);
insert into Person values ('David G' , 50);
insert into Person values ('Roger W' , 28);
insert into AgeFactor values (0,'Infant');
insert into AgeFactor values (2,'Toddler');
insert into AgeFactor values (4,'Child');
insert into AgeFactor values (13,'Adolescent');
insert into AgeFactor values (18,'Young Adult');
insert into AgeFactor values (30,'Adult');
insert into AgeFactor values (65,'Senior');
Then my first inclination would be to write the SQL using SQL-89 style joins, like this:
select p.Name, af.Factor
from Person p, AgeFactor af
where af.Age = ( select max(Age) from AgeFactor
where Age <= p.Age )
This statement would return a result set that looked like this:
Name Factor ---------- ----------- John B Adult John J Toddler Jimmy P Senior Robert P Adolescent Eric C Senior Syd B Adult David G Adult Roger W Young Adult
However, this works fine unless you have a person with an age less than the lowest age in your AgeFactor table. For example, if Infant and Toddler were not in your AgeFactor table, then John J would not be a part of your result set. Also, if a person had an unknown age, represented by NULL, then he or she would not show up in the result set. If you wanted every person to show up regardless, then your SQL might look like this:
SELECT p.Name, af.Factor
FROM Person p LEFT JOIN AgeFactor af
ON p.Age >= af.Age
WHERE af.Age is null
OR af.Age = ( select max(Age) from AgeFactor
where Age <= p.Age )
This is a variation of the first SQL, but using LEFT JOIN allows every person to show up. Checking if af.Age is null keeps the last WHERE condition from excluding rows without matching AgeFactor rows. Here is another way to write your SQL:
SELECT Name, Factor
FROM ( SELECT Name, max(af.Age) as AgeGroup
FROM Person LEFT JOIN AgeFactor af
ON Person.Age >= af.Age
GROUP BY Person.Name ) NameAge
LEFT JOIN
AgeFactor
ON NameAge.AgeGroup = AgeFactor.Age
Here, the subquery gives us all of the rows from Person with their age group. Then, we simply join that to the AgeFactor table to get the needed factor(s).
These queries will most likely perform differently at varrying row counts, so I would pick the one that best fits your environment.
This was first published in February 2002
Join the conversationComment
Share
Comments
Results
Contribute to the conversation