MySQL: how to write a query for a relationship that is based on a range, not a foreign key?
I have three tables in MySQL that are related, yet not technically linked to each other by a foreign key. They are: **users**, **levels**, and **classes**. 

The users table has a karma column, a numeric type. Based on this **karma** number, I want to know the user's **level**, which I retrieve from the levels table. This is not a hard relationship, since a level is associated with a **range** of karma values, like so:

 - level 1 (minimum karma: 0)
 - level 2 (minimum karma: 100)
 - level 3 (minimum karma: 500)
 - ...

So if a user has a karma of 400, the return value should be 2. To make things slightly more complex, the level number indicates the user's **class**, of which the definitions are stored in the classes table. Once again this is a **range** relationship:

- class ant (minimum level 1)
- class owl (minimum level 5)
- class lion (minimum level 100)
- ...

In summary, we're talking about three tables that have an implicit relationship with each other, based on range values. My question relates to how to effectively query these tables. A very common need for me is to get information for one or more users based on a condition, the result set should contain the user details, but also the user's level and class.

For a single user, I managed to write this query which works fine:

    SELECT u.*,  lv.num as level, lvc.title as class, lvc.id as classid
    FROM user as u, level as lv, levelclass as lvc
    WHERE u.id = ? AND lv.min_karma <= u.karma AND lv.num <= lvc.minlevel_num
    ORDER BY lv.num DESC LIMIT 1;

However, if I would broaden the result set by leaving WHERE u.id = ? and removing LIMIT 1, thus querying for a **list** of users, I get the combination of all three tables. Typically you would bring the rows down by doing an inner join on keys, but since this is a range check, 
that does not work. I tried using the range check in an inner join condition, but that brings the same result. Even using grouping I cannot get the result I want.

In a desperate attempt, I came up with this query, which works:

    SELECT usr.*, 
    (SELECT lv.num as level
    FROM user as u, level as lv, levelclass as lvc
    WHERE u.id = usr.id AND lv.min_karma <= u.karma AND lv.num <= lvc.minlevel_num
    ORDER BY lv.num DESC LIMIT 1) as level,
    (SELECT lvc.title as class
    FROM user as u, level as lv, levelclass as lvc
    WHERE u.id = usr.id AND lv.min_karma <= u.karma AND lv.num <= lvc.minlevel_num
    ORDER BY lv.num DESC LIMIT 1) as class,
    (SELECT lvc.image as class_image
    FROM user as u, level as lv, levelclass as lvc
    WHERE u.id = usr.id AND lv.min_karma <= u.karma AND lv.num <= lvc.minlevel_num
    ORDER BY lv.num DESC LIMIT 1) as class_image,
    (SELECT lvc.id as classid
    FROM user as u, level as lv, levelclass as lvc
    WHERE u.id = usr.id AND lv.min_karma <= u.karma AND lv.num <= lvc.minlevel_num
    ORDER BY lv.num DESC LIMIT 1) as classid
    FROM user as usr
    ORDER BY usr.$sortby $direction LIMIT ?,?

However, it seems highly inefficient to me. Basically what I do here is writing a sub query for each **column(!)** that I need from the level and class tables. If I'd query for multiple columns of the level or class tables in one subquery, it once again returns the combinations of all values. I feel there is a gap in my DB skills, something obvious I am missing, a function I do not know about...

**Can you help me? How to write an efficient query for a set of rows that combines columns from three tables, yet is not linked by keys (ranges instead)?**

**PS:** I know that I could greatly simplify the scenario if I would denormalize this schema to combine levels and classes into the users table, but there is a specific reason why I need this, trust me.
MySQL: how to write a query for a relationship that is based on a range, not a foreign key?
===

I have three tables in MySQL that are related, yet not technically linked to each other by a foreign key. They are: **users**, **levels**, and **classes**. 

The users table has a karma column, a numeric type. Based on this **karma** number, I want to know the user's **level**, which I retrieve from the levels table. This is not a hard relationship, since a level is associated with a **range** of karma values, like so:

 - level 1 (minimum karma: 0)
 - level 2 (minimum karma: 100)
 - level 3 (minimum karma: 500)
 - ...

So if a user has a karma of 400, the return value should be 2. To make things slightly more complex, the level number indicates the user's **class**, of which the definitions are stored in the classes table. Once again this is a **range** relationship:

- class ant (minimum level 1)
- class owl (minimum level 5)
- class lion (minimum level 100)
- ...

In summary, we're talking about three tables that have an implicit relationship with each other, based on range values. My question relates to how to effectively query these tables. A very common need for me is to get information for one or more users based on a condition, the result set should contain the user details, but also the user's level and class.

For a single user, I managed to write this query which works fine:

    SELECT u.*,  lv.num as level, lvc.title as class, lvc.id as classid
    FROM user as u, level as lv, levelclass as lvc
    WHERE u.id = ? AND lv.min_karma <= u.karma AND lv.num <= lvc.minlevel_num
    ORDER BY lv.num DESC LIMIT 1;

However, if I would broaden the result set by leaving WHERE u.id = ? and removing LIMIT 1, thus querying for a **list** of users, I get the combination of all three tables. Typically you would bring the rows down by doing an inner join on keys, but since this is a range check, 
that does not work. I tried using the range check in an inner join condition, but that brings the same result. Even using grouping I cannot get the result I want.

In a desperate attempt, I came up with this query, which works:

    SELECT usr.*, 
    (SELECT lv.num as level
    FROM user as u, level as lv, levelclass as lvc
    WHERE u.id = usr.id AND lv.min_karma <= u.karma AND lv.num <= lvc.minlevel_num
    ORDER BY lv.num DESC LIMIT 1) as level,
    (SELECT lvc.title as class
    FROM user as u, level as lv, levelclass as lvc
    WHERE u.id = usr.id AND lv.min_karma <= u.karma AND lv.num <= lvc.minlevel_num
    ORDER BY lv.num DESC LIMIT 1) as class,
    (SELECT lvc.image as class_image
    FROM user as u, level as lv, levelclass as lvc
    WHERE u.id = usr.id AND lv.min_karma <= u.karma AND lv.num <= lvc.minlevel_num
    ORDER BY lv.num DESC LIMIT 1) as class_image,
    (SELECT lvc.id as classid
    FROM user as u, level as lv, levelclass as lvc
    WHERE u.id = usr.id AND lv.min_karma <= u.karma AND lv.num <= lvc.minlevel_num
    ORDER BY lv.num DESC LIMIT 1) as classid
    FROM user as usr
    ORDER BY usr.$sortby $direction LIMIT ?,?

However, it seems highly inefficient to me. Basically what I do here is writing a sub query for each **column(!)** that I need from the level and class tables. If I'd query for multiple columns of the level or class tables in one subquery, it once again returns the combinations of all values. I feel there is a gap in my DB skills, something obvious I am missing, a function I do not know about...

**Can you help me? How to write an efficient query for a set of rows that combines columns from three tables, yet is not linked by keys (ranges instead)?**

**PS:** I know that I could greatly simplify the scenario if I would denormalize this schema to combine levels and classes into the users table, but there is a specific reason why I need this, trust me.