Subquery in from clause

bchamp · **Posted:** Thu Jun 22, 2006 6:16 pm

I'm trying to figure out how to work around Hibernate's limitation
of no subqueries allowed in the from clause. Here's the set up:

A store sells individual baseball cards to customers.

Code:

Table: Customer
cust_id
   32
   52
   71

Table: CustomerGroup
group_id
   12
   11

Table: GroupRef
group_id  cust_id
   12        32
   11        52
   12        71

Table: Card
card_id    team      league
   8801      A         East
   7232      A         East
   8827      B         East
   7749      C         West


Table: Purchase
cust_id   card_id
   52       8801
   32       7232
   71       8827
   52       7749
   52       8801
   52       7232
   32       8801

The Purchase table records each card that is sold. The Card table is inventory.
I need to build a report which looks like this:

Code:

Group 11 :4
  league East :3
    team A :3
      8801 :2
      7232 :1
  league West :1
    team C :1
      7749 :1

Group 12 :3
  league East :3
    team A :2
      7232 :1
      8801 :1
    team B :1
      8827 :1

The number after the colon is the sum of cards sold at that level.

In MySQL, I can run this SQL query:

Code:

select count(*) as theCount, cg.group_id, c.league, c.team, c.card_id 
from Purchase as p, Card as c, GroupRef as gr, CustomerGroup as cg 
where cg.group_id = gr.group_id and gr.cust_id = p.cust_id 
and p.card_id = c.card_id 
group by cg.group_id, c.league, c.team, c.card_id;

which yields a result set like this:

Code:

count(*)  groupId,  league  team  cardId
  1           11      East   A      7232
  2           11      East   A      8801
  1           11      West   C      7749
  1           12      East   A      7232
  1           12      East   A      8801
  1           12      East   B      8827

This gives me the basis for making the report where each row in the result set
is a leaf node on the report. The summed category headers on the report
require a little more work. I've found that if I run queries against the above
result set, I can get those values for the report.

For example, if I want to know how many were sold to group 11, I run this SQL query against
the result set by using a subquery:

Code:

select sum(tbl.theCount) from (
     select count(*) as theCount, cg.group_id, c.league, c.team, c.card_id 
     from Purchase as p, Card as c, GroupRef as gr, CustomerGroup as cg 
     where cg.group_id = 11 and cg.group_id = gr.group_id 
     and gr.cust_id = p.cust_id and p.card_id = c.card_id 
     group by cg.group_id, c.league, c.team, c.card_id) 
  as tbl;

Hibernate does not allow subqueries in the from clause, so I can't just translate this
into HQL. Is there a query to give me the sums I want? Views suggests themselves here, but
I've got some installations running with a DB that does not support views (MySQL pre-5.0).
Should I just force all stores to upgrade to a DB that supports views?

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Why not use an SQL query? Session.createSQLQuery will do all of this for you, using the query that you've already written.

bchamp · **Posted:** Fri Jun 23, 2006 12:21 am

tenwit wrote:

Why not use an SQL query? Session.createSQLQuery will do all of this for you, using the query that you've already written.

Yeah, good point. I like to keep to HQL if I can because it translates to the SQL required by the database for me. Maybe in this case the SQL is universal enough. Well, I guess either way, I've got to test it against all my supported database engines. Thanks.

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Everything in the query you wrote is legal in every SQL standard. So long as your DBMS supports any of the ANSI standards (SQL-86, 89, 92, 1999 or 2003) then your query will work.