Solved

HQL Join - Subqueries

Posted on 2014-01-15
6
958 Views
Last Modified: 2014-01-15
I am pretty good at doing basic queries but am trying to accomplish the following -

I have an Item master and an Item Detail that are related by item id.    The item detail can have 3 status - Transit, Stock, Invoiced

I would like my query to return how many of each item are in what status even if zero in any so my result would look something like this

                Transit         Stock        invoiced
item 1          5                7                25
item 2          0                0                20
item 3           5                5                0
item 4           0               0                 0
item 5           5              0                 5

tables:  SkuMaster, Tally
FK : Tally.skuId

I want to do something along the following but do not understand how to join properly

SELECT sm.skuId as sku, sm.tlcDescription as item ,

(SELECT count(t.tallyId) as shp FROM Tally t WHERE  t.status='Transit' group by t.skuMaster.skuId ),

(SELECT count(t.tallyId) as stk FROM Tally t WHERE  t.status='Stock' group by t.skuMaster.skuId),

(SELECT count(t.tallyId) as dlv FROM Tally t WHERE  t.status = ('Invoice') group by t.skuMaster.skuId)

from SkuMaster sm LEFT JOIN .......... ?   sm.tally.skuId?

Open in new window

0
Comment
Question by:TumacLumber
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 42

Expert Comment

by:pcelba
ID: 39783537
Try this:
SELECT sm.skuId as sku, sm.tlcDescription as item,
       SUM(CASE WHEN t.status='Transit' THEN 1 ELSE 0 END) AS shp,
       SUM(CASE WHEN t.status='Stock'   THEN 1 ELSE 0 END) AS stk,
       SUM(CASE WHEN t.status='Invoice' THEN 1 ELSE 0 END) AS dlv
  FROM SkuMaster sm
 INNER JOIN Tally t ON t.skuId = sm.skuId
 GROUP BY sm.skuId, sm.tlcDescription

It will ignore items having no records in Tally but you may add them in additional UNION query.
0
 

Author Comment

by:TumacLumber
ID: 39783614
HQL does not like the ON in the join
0
 
LVL 42

Accepted Solution

by:
pcelba earned 500 total points
ID: 39783652
OK, above query should work on MySQL server...

You may replace the ON  by WITH

or better:
SELECT sm.skuId as sku, sm.tlcDescription as item,
       SUM(CASE WHEN t.status='Transit' THEN 1 ELSE 0 END) AS shp,
       SUM(CASE WHEN t.status='Stock'   THEN 1 ELSE 0 END) AS stk,
       SUM(CASE WHEN t.status='Invoice' THEN 1 ELSE 0 END) AS dlv
  FROM SkuMaster sm, Tally t
 WHERE t.skuId = sm.skuId
 GROUP BY sm.skuId, sm.tlcDescription

BUT I am not sure about other construsts like GROUP BY and CASE WHEN...

According to this manual both GROUP BY and CASE WHEN should work: http://docs.jboss.org/hibernate/core/3.6/reference/en-US/html/queryhql.html#queryhql-expressions
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

Author Comment

by:TumacLumber
ID: 39783665
This looked like it worked.  Since I already have the FK relationship in HQL - your CASE statements were what I was looking for.   I will try the above to see if it worked as well.

SELECT t.skuMaster.skuId as sku, t.skuMaster.tlcDescription as item,
       SUM(CASE WHEN t.status='Transit' THEN 1 ELSE 0 END) AS shp,
       SUM(CASE WHEN t.status='Stock'   THEN 1 ELSE 0 END) AS stk,
       SUM(CASE WHEN t.status='Invoice' THEN 1 ELSE 0 END) AS dlv
  FROM Tally t

 GROUP BY t.skuMaster.skuId, t.skuMaster.tlcDescription


The above also worked with the small change

WHERE t.skuMaster.skuID - sm.skuId   (FK relationship which you had no real knowledge on )

Thanks for the help.   This will be re-used throughout my app!
0
 
LVL 42

Expert Comment

by:pcelba
ID: 39783725
You are welcome.  Interesting language this HQL...
0
 

Author Comment

by:TumacLumber
ID: 39783733
Yeah - it's a real beetch for intermediate level programmers like myself.   I am using Wavemaker to develop a couple apps and it is great except having to learn HQL.
0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
This video teaches viewers about errors in exception handling.

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question