Hi, I need help to create a mapping file between 2 tables with a non exact relational join
In my database, I have :
- one table Dictionary
Code:
Dictionary
(
Number int,
ItemCode int,
ItemDesignation string
)
where (Number,ItemCode) is a composite id
Code and designation example :
dictionary number 1 : gender
Quote:
1 : male
2 : female
dictionary number 2 : marital state
Quote:
1 : single
2 : married
3 : divorced
...
- one table Person
Code:
Person
(
Id int,
FirstName string,
LastName string,
BirthDate datetime,
GenderCode int,
MaritalStateCode int
)
- SQL relation between Person and Dictionary would be
Code:
SELECT FirstName,LastName,BirthDate,D1.ItemDesignation,D2.ItemDesignation
FROM Person,Dictionary D1,Dictionary D2
WHERE 1=D1.Number AND GenderCode =D1.ItemCode
AND 2=D2.Number AND MaritalStateCode=D2.ItemCode
Is it possible to express a many to one relation between Person and Dictionary for each join and load automatically an entity Person including 2 entities Dictionary gender and marital state
Thanks for help