Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

HQL Join - Subqueries

Posted on 2014-01-15
6
Medium Priority
?
979 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 43

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 43

Accepted Solution

by:
pcelba earned 2000 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

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 43

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:

670 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