Advice on a Query

[Wickersty]

Hi folks. I do basic and some intermediate SQL querying a lot, but the solution to this query escapes me... i'm not sure how to accomplish this. Could someone lend a hand?

Let's say I have two tables, with the following columns:

grades_tbl: id, grade_lvl, grade_txt
programs_tbl: id, program_name, grade_from_id, grade_to_id

both ids, grade_lvl, grade_from_id, and grade_to_id are integers. grade_txt and program_nam are varchars.

grade_txt would be something like "Pre-K", "K", "1", "Adult", etc. grade_lvl is a number representation, to give the grades a hiearchy, so that Pre-K's grade_lvl might be "-2" and "K" might be "-1" and 1 would be "1", and so on... with "Adult" maybe being "13".

The task at hand is I receive a grade_id from a user. Say, it's grade_id=3. I look in the grades_tbl and find out what the grade_lvl and grade_txt are for that id. grade_id winds up being grade_lvl=3, grade_txt=3. Easy. Now, I need to query the programs_tbl and find programs whose grade_from is below the grade that the user selected, and whose grade_to is above it. This is where I get stuck.

Essentially, I need to query grades_tbl to find out what grade_from_id's grade_txt and grade_lvl are, and find out what grade_to_id's grade_txt and grade_lvl are. Then, I need to query programs_tbl to find out for which programs the user-selected grade falls in between grade_from and grade_to. It's tricky b/c programs_tbl doesnt have grade_tdxt and grade_lvl info, only the identifier grade_id. There's got to be a tight way to do this instead of a nest of ugly single CFQUERYs.

Can anyone offer some advice or a solution? Many thanks for your kindness!!

Jeff

 

[gmmastros]

Can you show some sample data with expected results? It would help us a lot.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Wickersty]

Sure!

grades: (id, grade_lvl, grade_txt)
1, -2, Pre-K
2, -1, K
3, 1, 1
4, 2, 2
5, 3, Pre-Adult

programs: (id, prog_name, grade_from_id, grade_to_id)
1, Science, 2, 4
2, Math, 1, 4
3, Advanced Reading, 5, 5

Expected results:

User selects, from a pull down menu, Grade "1".
User is shown Science and Math because grade "1" falls in between their grade_from and grade_to, but is not shown Advanced Reading b/c grade "1" does NOT fall in between its grade_from and grade_to.

And note that the integer values in the grade_froms and grade_tos are identifiers pointing to grades table...


Thanks!

Jeff

 

[gmmastros]

Select P.*
From   Programs P
       Inner Join Grades As Grade
         On Grade.id Between P.grade_from_id And P.grade_to_id
Where	Grade.grade_txt = '1'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Wickersty]

You're the man George. Thank you for this. I never really grasped Inner Joins and the like... need to do some more studying on those.

Much appreciated!