Hi all,

using:
NHibernate 1.0.2
MySQL 4.1

tables:
SystemCode
(
SysItemID bigint(20) not null auto_increment,
GroupID bigint(20) not null,
ItemID bigint(20) not null,
ItemDesc varchar(100) not null,
DisplayInd tinyint(4) not null
)

data sample:
1,1000,1001,Accessories,1
2,1000,1002,Consumables,1
3,1000,1003,Phones,1
.....
25,2000,2001,Cases,1
26,2000,2002,headphones,1
.....

GroupMapping
(
MappingID bigint(20) not null auto_increment,
GroupID bigint(20) not null,
SubGroupID bigint(20) not null)
)

data sample:
1,1001,2001
2,1001,2002
3,1001,2003
.....

SystemCode table holds all sorts of system related information (labels) that are groupped by GroupID
eg GroupID 1000 = all top level groups like Accessories, Consumables, Phones....etc
GroupID 2000 = are subgroups of the top level groups - think online store search facility.

GroupMapping table provides the mapping between top level groups and their subgroups - one- to-many.

If I put together standard SQL it would look something like this:

select c.ItemID, c.ItemDesc
from SystemCode a
join GroupMapping b on a.ItemID = b.GroupID
join SystemCode c on c.ItemID = b.SubGroupID and c.DisplayInd = 1
where b.GroupID = 1001 /* this will be a parameter */
and a.DisplayInd = 1
order by c.ItemDesc

This would give me a list of SystemCode items for all the "Accessories" for example.

Can I achieve this by creating a mapping (hbm) file or do I need to use HQL? If I can use hbm what would it look like?

Thanks in advance

HBM mapping is suitable. Something along these lines:You may alternatively want to implement this a "TopLevelGroup", "SecondLevelGroup", etc.
Then you'd put 'where="(GroupID >= 1000 and GroupID < 2000)"' in the TopLevelGroup mapping, 2000 <= GroupID < 3000 for second level, etc.