Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Selecting income range by SQL

 
Jacky Luk
Ranch Hand
Posts: 634
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Here I've got 3 persons, 1 with $1000.00, 2nd with $10000, 3rd with $2000
The "count(if" statement results in 15 persons, why is that?
Thanks
Jack

 
K. Tsang
Bartender
Posts: 3583
16
Android Java
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Not knowing your data, but you seem to be doing a join " ... from customer, income_range". Shouldn't there be a key to join these 2 tables together?
 
Jacky Luk
Ranch Hand
Posts: 634
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
K. Tsang wrote:Not knowing your data, but you seem to be doing a join " ... from customer, income_range". Shouldn't there be a key to join these 2 tables together?



Not sure if my data model is little bit wrong
My customer table has an income value
And
My income_range table has a key with a LowerLimit and UpperLimit fields,
How do I join them together?
Thanks
Jack
 
K. Tsang
Bartender
Posts: 3583
16
Android Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I see what you mean. From the logical sense, there is no clear key to join these 2 tables.

I presume your goal is to determine if the customer is within/between some income range defined by that lower and upper limit.

I suggest you make a view (optional) showing just the income range's lower and upper limit. Then make a SQL function or java function to check the customer's income is within range.

If you use Java you may want to check out the Apache commons lang NumberRange class (org.apache.commons.lang.math.NumberRange).

If you use SQL function ... I'm sure you can figure it out.
 
Jacky Luk
Ranch Hand
Posts: 634
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
K. Tsang wrote:I see what you mean. From the logical sense, there is no clear key to join these 2 tables.

I presume your goal is to determine if the customer is within/between some income range defined by that lower and upper limit.

I suggest you make a view (optional) showing just the income range's lower and upper limit. Then make a SQL function or java function to check the customer's income is within range.

If you use Java you may want to check out the Apache commons lang NumberRange class (org.apache.commons.lang.math.NumberRange).

If you use SQL function ... I'm sure you can figure it out.


Thanks K Tsang,
Got it working, thank you, that's so nice of you, cheers
Jack
 
Consider Paul's rocket mass heater.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic