I'm having problems getting the following right.

I have four tables Product, ProductCharacteristic and ProductHasCharacteristic

Product : ProductHasCharacteristic = 1 : many

ProductCharacteristic : ProductHasCharacteristic = 1 : many

I want to show one specific ProductCharacteristic of a Product. If it does not have that characteristic, It is supposed to have a default value (or blanc).

I have managed to set up a TypedList for all Products that have a characteristic. but not to show the default value in case the specific value is not present.

I could set this up using views in SQL and then use a typed view or list in LLBLGen.

E.g.

Define view with name Xxxxxxx:

SELECT x.IdProductHasCharacteristic as IdProduct, y.name as SpecificCharacteristic FROM ProductHasCharacteristic x, ProductCharacteristic y where y.characteristicCode = 6

This gives me a table of the Ids of all ProductHasCharacteristic records together with the Name of the characteristic Iám looking for.

Then the following SQL would give me what I want:

SELECT a.Id, b.SpecificCharacteristic from Product a LEFT JOIN Xxxxxxx b ON a.Id = b.ProductId

Probably there is a better way to do this in SQL, but can this be doen in a typed list?

JayBee wrote:

Probably there is a better way to do this in SQL, but can this be doen in a typed list?

Yes. This is what you should do:

1. Create your DB View Xxxxxx;

2. Refresh the Catalog at LLBLGen Pro Designer and Add the Xxxxxx view as an Entity. You should do this right-clicking the Entities node and click "Add entity mapped onto view". Now you have the view and you see it as an entity Would be a good idea to set a primary key for that new Entity (XxxxxxEntty) so you can create a relation later.

3. Create a relation betwenn Product and Xxxxxx.

4. Create a TypedList based on the entities ProductEntity and XxxxxxEntity. There you should specify the correct Join Hint to the relation.

Hope that helps

The question was if it could be done completely in LLBLGen, without the creation of 1 or more views.

If I have to create a view for this, why not solve it completely within the database and use a typed view in LLBLGEN?

Define view with name Xxxxxxx:

SELECT x.IdProductHasCharacteristic as IdProduct, y.name as SpecificCharacteristic FROM ProductHasCharacteristic x, ProductCharacteristic y where y.characteristicCode = 6

This gives me a table of the Ids of all ProductHasCharacteristic records together with the Name of the characteristic Iám looking for.

Then the following SQL would give me what I want:

SELECT a.Id, b.SpecificCharacteristic from Product a LEFT JOIN Xxxxxxx b ON a.Id = b.ProductId

Please correect me if I'm wrong, would the following Query give the same results?

SELECT a.Id, b.name
FROM Product a
LEFT JOIN ProductHasCharacteristic ON......
LEFT JOIN SpecificCharacteristic b ON........
WHERE b.characteristicCode = 6

If yes, then this can be implemented by a TypedList, specifying LeftJoins for the appropriate relations.

To do so would require to first select the ProductHasCharacteristics belonging to ProductCharacteristic with Code 6. Use the result as an intermediate table and then do the LEFT OUTER JOIN between Product and the intermediate table.

Is it possible to define a typed list with the WHERE clause as part of the definition?

I don't get it, why do you want to specify the filter in the TypedList defininstion rather than passing it when querying the TypedList? Which in turn will append the filter to the query send to the server.

I think that the way you suggest will result in all HasProductCharacteristic records being selected.

I just want the ones that have ProductCode equal to 6 and merge that table with a LEFT JOIN with Product.

Would the end results differ?

What if later on you need results corresponding to characteristicCode = 7, would you create another TypedList and regenerate the code again? Or would it be better to pass this at runtime as a filter.

As I mentioned in the previous mail, all HasProductCharacteristic records would be selected. Therefore the endresult does differ.

I am now using a view that has characteristicCode = 6 fixed in it. That functions well.