LEFT OUTER JOIN in NHibernate with SQL semantics

Posted by Yuval on Stack Overflow See other posts from Stack Overflow or by Yuval
Published on 2010-04-01T09:37:29Z Indexed on 2010/04/01 9:43 UTC
Read the original article Hit count: 384

Filed under:

nhibernate

|

left

|

join

|

subquery

Hi,

Is it possible to use HQL/ICritera to produce a query with the same semantics as the following SQL query:

select table1.A, table2.B, count(*) from table1 
  left join (select table2.parent_id, table2.B from table2 where table2.C = 'Some value') as table2
on table2.parent_id = table1.id
group by table1.A, table2.B
order by table1.A

In particular, what I'd like is to receive rows (that is, objects) from table1 that have no matching rows in table2. However, I only get the rows from table1 that have matches in table2. Is this the meaning of 'LEFT JOIN' in HQL? And if so, how can I get it to join on a subquery?

Tnx.

© Stack Overflow or respective owner

Related posts about nhibernate

How can I eager-load a child collection mapped to a non-primary key in NHibernate 2.1.2?

as seen on Stack Overflow - Search for 'Stack Overflow'
Hi, I have two objects with a many-to-many relationship between them, as follows: public class LeftHandSide { public LeftHandSide() { Name = String.Empty; Rights = new HashSet<RightHandSide>(); } public int Id { get; set; } public string Name { get; set;… >>> More
Nhibernate 2.1 and mysql 5 - InvalidCastException on Setup

as seen on Stack Overflow - Search for 'Stack Overflow'
Hello there, I am trying to use NHibernate with Spring.Net und mySQL 5. However, when setting up the connection and creating the SessionFactoryObject, I get this InvalidCastException: NHibernate seems to cast MySql.Data.MySqlClient.MySqlConnection to System.Data.Common.DbConnection which causes… >>> More
Nhibernate transactions:Avoiding Nhibernate dependency in the service layer

as seen on Stack Overflow - Search for 'Stack Overflow'
One of practices advocated by Nhibernate experts is to do all actions in inside a transaction. In a typical 3 tier web architecture, Nhibernate depedency is limited the data layer. In such a case is it fine to use using (var tr = NHibernateSession.Current.BeginTransaction()) { at the controller… >>> More
Series of abstract classes and NHibernate

as seen on Stack Overflow - Search for 'Stack Overflow'
Hello, and first off thanks for your time to look at this. For a research project I'm working on, I have a somewhat complex design (which I've been given) to persist to a database via NHibernate. Here's an example of the class hierarchy: TransitStrategy, TransportationCompany and TransportationLocation… >>> More
Using Time columns with NHibernate, Fluent NHibernate and SQL Server 2008

as seen on Stack Overflow - Search for 'Stack Overflow'
Hello, I have a table with a time column in my SQL Server 2008 database. The property of the object I'm trying to map to is a TimeSpan. How can i tell FluentNHibernate to use the TimeAsTimeSpan NHibernate type, so that I don't have cast problems? >>> More

Related posts about left

Twitter Bootstrap styling conflicts with plug-ins like jqGrid and other third part libraries

as seen on Geeks with Blogs - Search for 'Geeks with Blogs'
Issues:The concern is that the Twitter Bootstrap framework is that some of their css selectors are simply too generic and have incompatibility issues and conflicts with most third party plug-ins and css libraries, like jQuery-UI and jqGrid.My most pressing concern is only with the generic selector… >>> More
Planning Database as a Service Implementation Project

as seen on Oracle Blogs - Search for 'Oracle Blogs'
People, process and planning are the three key elements to success in a private cloud journey. Some common questions i hear from field/customers often relates to tasks involved in setting up Database-as-a-Service(DBaaS) using Oracle Enterprise Manager 12c from scratch and how these tasks are mapped… >>> More
Basic Question : LEFT OUTER JOIN vs Left Join

as seen on Stack Overflow - Search for 'Stack Overflow'
What is the difference between between Left Join and Left Outer Join? >>> More
SQL SERVER- Differences Between Left Join and Left Outer Join

as seen on SQL Authority - Search for 'SQL Authority'
There are a few questions that I had decided not to discuss on this blog because I think they are very simple and many of us know it. Many times, I even receive not-so positive notes from several readers when I am writing something simple. However, assuming that we know all and beginners should know… >>> More
FLEX: how can I set bottom-left coordinate of my component instead of the top-left

as seen on Stack Overflow - Search for 'Stack Overflow'
hi, I've some components with dynamic heights. They have to be aligned with respect to the bottom of my canvas container, so I cannot set the same y for all components. I could compute their heights and successively set the y but I was wondering if there was an easier way to do it. thanks >>> More