CODEWITHSUNDEEP
hibernatecount
Arek
Arek

Reputation: 71

Hibernate count from multi tables

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?

Upvotes: 3

Views: 1351

Answers (1)

Arek
Arek

Reputation: 71

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.

Upvotes: 4

Related Questions