asked on

Inner Join Between 2 Values, and Cast

Hi Experts,

I need help in writing an INNER JOIN between 2 values and casting.

Here are the tables

mysql> describe noc_careers;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| id             | int(11)      | NO   | PRI | NULL    | auto_increment |
| noc_code       | varchar(5)   | NO   |     | NULL    |                |
| noc_occupation | varchar(255) | NO   |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> describe noc_categories;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| category   | varchar(255) | NO   |     | NULL    |                |
| range_from | smallint(6)  | NO   | MUL | NULL    |                |
| range_to   | smallint(6)  | NO   | MUL | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

mysql>

Open in new window

I need to return everything from noc_careers and the noc_categories.category. The relationship is where noc_careers.noc_code is within (>= noc_categories.range_from AND <= noc_categories.range_to).

To add to the complexity, note that noc_careers.noc_code is VARCHAR, so I also need to cast it.

Any help will be greatly appreciated.

PortletPaul

range_from and range_to are small integers

so they cannot refer to to varchar values, hence I doubt you need a type conversion

What does strike me is that if range_from and range_to refer to the id of another table is:
Why are they small integers but the ID is integer?
Quite strange.

I suggest your join may be like this:

from noc_careers as n
inner join noc_categories as c on n.id between c.range_from and c.range_to

[edit]
What I would really like to see is some sample data (from each table), and the expected result of the wanted query

ASKER CERTIFIED SOLUTION

Raja Jegan R

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial