File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Rajkamal Pillai
Ranch Hand
Posts: 445
1
Java Spring
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Your query is made very difficult by the bad data modelling. If you've defined this table, I would perhaps rethink it.
 
Rajkamal Pillai
Ranch Hand
Posts: 445
1
Java Spring
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


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
author
Saloon Keeper
Posts: 3900
16
Eclipse IDE Flex Google Web Toolkit
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Ireneusz Kordal
Ranch Hand
Posts: 423
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 445
1
Java Spring
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author
Saloon Keeper
Posts: 3900
16
Eclipse IDE Flex Google Web Toolkit
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic