aspose file tools*
The moose likes JDBC and the fly likes SQL query doubt about counting based on a condition and selecting rows that comply. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL query doubt about counting based on a condition and selecting rows that comply." Watch "SQL query doubt about counting based on a condition and selecting rows that comply." New topic
Author

SQL query doubt about counting based on a condition and selecting rows that comply.

Rajkamal Pillai
Ranch Hand

Joined: Mar 02, 2005
Posts: 443
    
    1

Hi,

I am trying to fetch the Names and Ages of the users who have a maximum of 2 children between the ages stored in 'min_child_age' and 'Max_child_age' in each row.

Users
- ID.
- Name
- Age
- No. of children
- Min_child_age
- Max_child_age
- Age_child_1
- Age_child_2
- Age_child_3
- Age_child_4

I tried using the query below. But it does not give the correct answer. When I try to use a COUNT() in the where clause, the SQL becomes invalid.

SELECT DISTINCT Name, Age
FROM Users
WHERE (No_of_children <= 2
AND ((CHILD_AGE1 >= CHILD_MIN_AGE
AND CHILD_AGE1 <= CHILD_MAX_AGE)
OR (CHILD_AGE2 >= CHILD_MIN_AGE
AND CHILD_AGE2 <= CHILD_MAX_AGE)
OR (CHILD_AGE3 >= CHILD_MIN_AGE
AND CHILD_AGE3 <= CHILD_MAX_AGE)
OR (CHILD_AGE4 >= CHILD_MIN_AGE
AND CHILD_AGE4 <= CHILD_MAX_AGE)));

Any suggestions?

Raj.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Your query is made very difficult by the bad data modelling. If you've defined this table, I would perhaps rethink it.

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Rajkamal Pillai
Ranch Hand

Joined: Mar 02, 2005
Posts: 443
    
    1



Better normalization would involve adding another table for the parent-children relation, I suppose. But the requirement is such that at any time there would only support a maximum of 4 and my take is that adding another table would make the already 'broad' ER relation broader. Apart from that perspective I am inclined to believe that there would not be further reaching consequences?

Even if we were to allow another table the issue would still persist, would it not and there is a plan to move the later releases onto Hibernate which I think allows for 'one-table-per-class' approach which would allow for this data model and it would also make the associations/mappings simpler?

I would like to hear your thoughts as if not for now I'm sure they would certainly come in handy sooner than later.
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

Paul is correct, this is a situation where normalization would buy you alot. For example, if you needed to store the names, birthdates, or other information for each child you would have to add 4 columns per field which would get ugly fast. As you've already seen, queries against this data is overly complicated. Performance would likewise be compromised in your current model. Finally, there is the obvious limit that a parent can have only 4 children that a normalized solution would resolve.

Yes, there is a cost associated with moving to a more-normalized data model AND I don't always recommend normalizing every field, but IMHO you've already hit the point it should have been done long ago.


My Blog: Down Home Country Coding with Scott Selikoff
Ireneusz Kordal
Ranch Hand

Joined: Jun 21, 2008
Posts: 423
Hi,

I agree that data structure is a little uncommon
Anyway if you have to use it, you may try this query:


Rajkamal Pillai
Ranch Hand

Joined: Mar 02, 2005
Posts: 443
    
    1

Thank you all for the valuable suggestions.

Now given the query how do I transform it to Hibernate understandable form using Criteria/Restrictions (I mean without having to use HQL)?

What if I were to be able to modify the Db structure by adding a few more normalization principles?

E.g.




Raj.
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

I prefer your new structure although many people would tell you to delete "no of children" since it can be looked up from the child table. Now this is a more interesting normalization question! If writes are far less common then reads (IE, people aren't adding/removing children all that often) it may make sense to have the children count duplicated in this table if its read often since it would avoid the table join.
 
Don't get me started about those stupid light bulbs.
 
subject: SQL query doubt about counting based on a condition and selecting rows that comply.