HQL Join - Subqueries

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

TumacLumberAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
pcelbaConnect With a Mentor Commented:
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
 
pcelbaCommented:
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
 
TumacLumberAuthor Commented:
HQL does not like the ON in the join
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
TumacLumberAuthor Commented:
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
 
pcelbaCommented:
You are welcome.  Interesting language this HQL...
0
 
TumacLumberAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.