Hello.
I have these two tables:
Person
personID
personName
personAddress
CountryID
GroupID
houseID
Group
groupID
groupName
Now, what I want to do, is to select a person's ID, its name, and join this with Group, so that I'll get a resultset like this:
Code:
personID personName groupID groupName
1 john 1 groupONE
2 james (null)
3 carl (null)
7 dan 2 groupTWO
8 joe (null)
10 paul (null)
I am using a join like this:
select p.personID,p.personName,Group from Person p left JOIN p.Group as Group
What I seem to not understand, is how I can map or join two tables that are connected through other things than the primary key.
Right now my mapping file for "person" looks something like this:
<hibernate-mapping >
<class name="Person" table="Person" >
<id name="personID" column="personID" type="int" unsaved-value="0">
<generator class="identity"/>
</id>
<property name="personName" column="personName" type="java.lang.String" />
<property name="personAddress" column="personAddress" type="java.lang.String" />
<property name="CountryID" column="CountryID" type="int" />
<property name="GroupID" column="GroupID" type="int" />
<property name="houseID" column="houseID" type="int" />
<set name="Group" table="Group" inverse="true" cascade="all" lazy="true">
<key column="GroupID" />
<one-to-many class="Group" />
</set>
</class>
</hibernate-mapping>
This gives me the problem that the join will be performed on Group.GroupID=Person.personID, which is not what I want.
What I want is a join on Group.GroupID=Person.GroupID .... I am sure that it is my mapping file that is incorrect, but I have no idea what it should look like in order to be able to create a join like this.
P.S : There might be errors in the code above, it has been simplified / examplified from the original in order to make it a little more clear what I'm after.
Regards C.H