Hello everyone! I'm not new to Hibernate, but I am learning .Net. Could I get some pointers on how to convert this old SQL query into hql?

SELECT dbo.BillingAccount.BillingAccountId, dbo.Customer.CustomerName, dbo.BillingAccount.BillingAccountNumber,
(SELECT COUNT(*) AS Expr1
FROM dbo.Device
WHERE (BillingAccountId = dbo.BillingAccount.BillingAccountId)) AS DeviceCount, dbo.BillingAccount.Notes, dbo.BillingAccount.LastModified
FROM dbo.BillingAccount LEFT OUTER JOIN
dbo.AuthUser ON dbo.AuthUser.AuthUserId = dbo.BillingAccount.LastModifiedBy LEFT OUTER JOIN
dbo.Customer ON dbo.Customer.CustomerId = dbo.BillingAccount.CustomerId
WHERE (dbo.BillingAccount.Deactivated IS NULL)

I have two problems:

How do I sub-select within HQL (for the count(*)).

How do I join on non(domain) relational entities? ie: on the first join here, BillingAccount is not related to AuthUser, but the lastmodifiedby column is the ID of the AuthUser that last modified that entity.

Thanks in advance!!!

Ok, I found my answer for counting elements - here's my current query.

select BA.Id, C.CustomerName, BA.BillingAccountNumber, count(elements(BA.DeviceSet)) from BillingAccount BA left join BA.Customer C group by BA.Id, C.CustomerName, BA.BillingAccountNumber

The only thing I'm stuck on is how to join to a non-relational field

Any suggestions?