version 1.0.2005.1 final (self-servicing) VS2005 asp.net 2.0

Hiya,

I have an issue that is similar to a diferent problem that I haven't quite managed to crack yet.

I need to create a typedList that displays values from a self-referencing table.

<schema> tblPerson personId personName motherId FK tblPerson.personId countryId FK tblCountry.countryId </schema>

I have created my typedList via the designer. It now displays all the fields, including (countryName). So, I know that I can get the relations to work via the designer.

The issue is that I can't get it to display the name of the "mother" of the person in question.I have: 1) added the tblPerson (twice) 2) created the alias (mother)

In the "fields mapped on entity fields", I have selected the "mother.PersonName" field. I generate the llblGenPro project, which in turn, reloads the newly generated c# dal project in my solution.

I then rebuild and run my visual studio solution.

The column header for the "mother.personName" is visible in the grid as "personName_" The actual value for the column is the SAME as that of the person..it should be the mother's name that is displayed here.

I can confirm that:

1) the relationships in the database are correct. 2) the motherId in tblPerson reflects a valid value.

Can anyone help me troubleshoot this?

As I say, this is similar to a previous problem that I had, but I've had to back up and make it a bit simpler, so that I can eventually get my head round it.

Any advice appreciated.

many thanks,

yogi

Would you please check the generated query, post it here, manually run it against the database, and post the results here against the results you get from LLBLGen Pro?

Also please post the LLBLGen Pro runtime library version used?

hiya Walaa,

generated query:

SELECT [dbo].[tblPerson].[personId] AS [PersonId], [dbo].[tblPerson].[personName] AS [PersonName], [dbo].[tblCountry].[CountryName], 
[LPA_M1].[personName] AS [PersonName_] FROM (( [dbo].[tblCountry]  INNER JOIN [dbo].[tblPerson]  ON  
[dbo].[tblCountry].[CountryId]=[dbo].[tblPerson].[countryId]) INNER JOIN [dbo].[tblPerson] [LPA_M1]  ON  
[dbo].[tblCountry].[CountryId]=[LPA_M1].[countryId])

both llblGenpro and manually run database query return the same results:

personId personName country personName_ 0 sonName UK sonName 0 sonName UK mumName 0 sonName UK dadName

1 mumName UK sonName 1 mumName UK mumName 1 mumName UK dadName

So, the issue is that:

1) it should only return a SINGLE row for each person. 2) personName_ should return the "mother" of the person in question.

version 1.0.2005.1 final (self-servicing) VS2005 asp.net 2.0

So, I think that it should be using a DISTINCT somewhere in the typedList, but I'm not sure what else.

I included the "country" column, just to illustrate that it has no problems with related tables, and that the issue probably lies within the fact that I am self-referencing a table.

Please let me know if I can clarify anything.

many thanks,

yogi

FROM (( [dbo].[tblCountry] INNER JOIN [dbo].[tblPerson] ON [dbo].[tblCountry].[CountryId]=[dbo].[tblPerson].[countryId]) INNER JOIN [dbo].[tblPerson] [LPA_M1] ON [dbo].[tblCountry].[CountryId]=[LPA_M1].[countryId])

Reading the above JOINS, I suppose you are using the wrong set of relations in your TypedList, it seems that you added the Person table for the second time linked to the country table, not with the first instance of the Person table.

Please check the relations in TypedList edit/prperties in the Designer.

hiya Walaa.

Thanks, I re-created from scratch and it's a bit better.It's still not quite right though.

Yes, it now displays distinct rows, instead of duplicates, but it omits several field values.

Let's forget about the "country" field, this displays correctly.

What I want to display is the following:

personId    personName     mumName

0               son                   mum       
1               mum                  gran
2               gran                      empty

what is actually displayed is:

personId    personName     mumName
0                son                      mum  
1               mum                  gran

In other words, it doesn't display anyone that it doesn't deem to have a "mum" Why would this be, I have set a LEFT-JOIN, to deal with nullable FK's?

sql below:

SELECT [dbo].[tblPerson].[personId] AS [PersonId], [dbo].[tblPerson].[personName] AS [PersonName], [LPA_M1].[personName] AS [Mother], 
[dbo].[tblCountry].[CountryName] FROM (( [dbo].[tblPerson]  RIGHT JOIN [dbo].[tblPerson] [LPA_M1]  ON  [dbo].[tblPerson].[personId]=[LPA_M1].[motherId]) 
INNER JOIN [dbo].[tblCountry]  ON  [dbo].[tblCountry].[CountryId]=[LPA_M1].[countryId])

I think the designer is perhaps adding relations that I don't need. As I say, the mother alias "personName" is the only field that I am having issues with.

How could I solve this?

many thanks,

yogi

SELECT [dbo].[tblPerson].[personId] AS [PersonId], [dbo].[tblPerson].[personName] AS [PersonName], [LPA_M1].[personName] AS [Mother], [dbo].[tblCountry].[CountryName] FROM (( [dbo].[tblPerson] RIGHT JOIN [dbo].[tblPerson] [LPA_M1] ON [dbo].[tblPerson].[personId]=[LPA_M1].[motherId]) INNER JOIN [dbo].[tblCountry] ON [dbo].[tblCountry].[CountryId]=[LPA_M1].[countryId])

Running the above query in the database would geive the correct results or not?

Anyway would you please attach the lgp project file?

hiya Walaa,

Unfortunately that query didn’t give me what I needed. The tables actually involve “dog” family trees, not those of people. (I just wanted to simplify, for the sake of clarity) I include the lgp project.

What I essentially want to do is pull the following fields for each dog.

dogId registeredName damId tblDog.dogId (self reference to the “registedName” of the mother dog) Country (tblCountry.countryId)

So, Here are 3 dogs:

dogId registeredName mumName 0 gran empty 1 mum gran 2 son mum

I hope that the above is clear.Please let me know if I should clarify anything.

Many thanks,

yogi

yogiberr wrote:

hiya Walaa.

Thanks, I re-created from scratch and it's a bit better.It's still not quite right though.

Yes, it now displays distinct rows, instead of duplicates, but it omits several field values.

Let's forget about the "country" field, this displays correctly.

What I want to display is the following:

personId    personName     mumName

0               son                   mum       
1               mum                  gran
2               gran                      empty

what is actually displayed is:

personId    personName     mumName
0                son                      mum  
1               mum                  gran

In other words, it doesn't display anyone that it doesn't deem to have a "mum" Why would this be, I have set a LEFT-JOIN, to deal with nullable FK's?

sql below:

SELECT [dbo].[tblPerson].[personId] AS [PersonId], [dbo].[tblPerson].[personName] AS [PersonName], [LPA_M1].[personName] AS [Mother], 
[dbo].[tblCountry].[CountryName] FROM (( [dbo].[tblPerson]  RIGHT JOIN [dbo].[tblPerson] [LPA_M1]  ON  [dbo].[tblPerson].[personId]=[LPA_M1].[motherId]) 
INNER JOIN [dbo].[tblCountry]  ON  [dbo].[tblCountry].[CountryId]=[LPA_M1].[countryId])

I think the designer is perhaps adding relations that I don't need. As I say, the mother alias "personName" is the only field that I am having issues with.

How could I solve this?

many thanks,

yogi

Yogi, I just reviewed your lgp. I think you are join hinting the wrong relation. Could you try this for me:

1. Open 'Edit Properties' of your TListDogList.
2. Go to 'Entity selection tab'
3. At 'Relation Description' select the relation Monther.DamId - TblDog.DogId (m:1)
4. In 'Select alternative' ComboBox select TblDog.DogId - Monther.DamId relation and click 'Set'.
5. Apply a Left Join Hint.
6. Regenerate.

Let us know how it works.