Hibernate count from multi tables

Question

I would like to write a single HQL query that return a specific count from 4 tables in different columns.

I have 4 tables: Tab1, Tab2, Tab3 and Tab4, and I would like to get something like this:

|Tab1   | Tab2  | Tab3  | Tab4   |
|..1....|..13...|...7...|....0...|

All records in Tab#s have their own id and ObjectID and ObjectFetchcolumns

I have tried something like this:

select DISTINCT
(select count(*) from Tab1 where ObjectFetch=:fetch and ObjectID=:id),
(select count(*) from Tab2 where ObjectFetch=:fetch and ObjectID=:id),
(select count(*) from Tab3 where ObjectFetch=:fetch and ObjectID=:id),
(select count(*) from Tab4 where ObjectFetch=:fetch and ObjectID=:id),
from Tab1, Tab2, Tab3, Tab4

But it doesn't work at all, any idea why?

Well, whould suggest you to use a native query to solve your problem, if you have no HQL based answers. — Crferreira, Aug 14 '13 at 12:53
Thank you, understand. But if in the future i have similar problem that answer is HQL based it would be a nice example. Thx one more time, but if you or someone else know the HQL for something like this it would help me a lot. — Arek, Aug 14 '13 at 13:02

score 4 · Answer 1 · edited Mar 28 '18 at 10:14

I found a solution, simply there is no need to use all tables after FROM we can use only 1, it is just for a correct syntax, also we can use a virtualTable, so the right HQL

select DISTINCT
(select count(*) from Tab1 where ObjectFetch=:fetch and ObjectID=:id),
(select count(*) from Tab2 where ObjectFetch=:fetch and ObjectID=:id),
(select count(*) from Tab3 where ObjectFetch=:fetch and ObjectID=:id),
(select count(*) from Tab4 where ObjectFetch=:fetch and ObjectID=:id)
from Tab1

and its all ok :) thx.

Hibernate count from multi tables

1 Answers1

Linked