I'm trying to construct the HQL equivalent for the following SQL and I'm at a loss (and extremly frustrated - this *should* be simple). I've tried a number of things but I have not had any luck. Any help is greatly appreciated.

My SQL:

I have an id for a record in the A table. I want a list of records (ids and names only) in the B table that are associated with the known record in the A table. I would typically write my SQL as follows.

SELECT B.c_id, B.c_name from B, C WHERE C.c_a_id = <id> AND C.c_b_id = B.c_id

Hibernate version:

2.1.7

Mapping documents:

<class name="eg.A" table="A">
<meta attribute="implement-equals">true</meta>
<id column="c_id" name="id" unsaved-value="null">
<generator class="native" />
</id>
<property column="c_a" name="a" not-null="true" type="string" />
<set name="bs" table="C" cascade="all">
<key column="c_a_id" />
<many-to-many column="c_b_id" class="eg.B" />
</set>
</class>

<class name="eg.B" table="B">
<meta attribute="implement-equals">true</meta>
<id column="c_id" name="id" unsaved-value="null">
<generator class="native" />
</id>
<property column="c_name" name="name" not-null="false" type="string" />
<property column="c_value" name="value" not-null="false" type="string" />
</class>

Database schema:

create table A {
c_id integer not null,
c_a varchar(255),
primary key (c_id)
}

create table B {
c_id integer not null,
c_name varchar(255),
c_value varchar(255),
primary key (c_id)
}

create table C {
c_a_id integer not null,
c_b_id integer not null,
primary key (c_a_id, c_b_id)
}

Value objects:

public class A {
private Integer id;
private Set bs;
public A() {}
public Integer getId() { return id; }
public void setId(Integer id) { this.id = id; }
public Set getBs() { return bs; }
public void setBs(Set bs) { this.bs = bs; }
}

public class B {
private Integer id;
private String name;
private String value;
public B() {}
public Integer getId() { reutrn id; }
public void setId(Integer id) { this.id = id; }
public String getName() { return name; }
public void setName(String name) { this.name = name; }
public String getValue() { return value; }
public void setValue(String value) { this.value = value; }
}

Kelly

I found a solution!!! I was making things too complicated I guess.

1. This returns a list of the eg.B objects associated with the eg.A object whose id is <id>.

select elements(a.bs) from eg.A as a where a.id=<id>

2. This returns a list of object arrays ([0]=eg.B id, [1]=eg.B name) associated with the eg.A object whose id is <id>.

select b.id, b.name from eg.A as a join a.bs as b where a.id=<id>

Kelly