Solved

HQL Join - Subqueries

Posted on 2014-01-15
6
949 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.

728 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