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

hiya, I have 2 tables

tblJobCategory jobCategoryId PK jobCategoryName

tblPerson personId PK jobCategoryIdOne (can contain null) jobCategoryIdTwo (can contain null)

I need to filter the jobCategories, displaying ONLY the jobCategories that are present in either

tblPerson.jobCategoryIdOne
or tblPerson.jobCategoryIdTwo

the jobCategory id's in tblPerson will only come from tblJobCategory, but they are optional, and are therefore not foreign keys.

eg
tblJobCategory jobCategoryId 1 jobCategoryName builder

tblJobCategory jobCategoryId 2 jobCategoryName chef

tblJobCategory jobCategoryId 3 jobCategoryName mechanic

if no-one in tblPerson has the jobCategoryId 3 (mechanic)

Then the jobCategory dropdown will only contain:

builder chef

BusinessCategoryCollection cats = new BusinessCategoryCollection();

PredicateExpression filter = new PredicateExpression();

cats.GetMulti(filter)?????

I hope that makses sense.

many thanks,

yogi

The following should be your SQL query:

SELECT * FROM tblJobCategory
WHERE 
jobCategoryId IN ( SELECT DISTINCT jobCategoryIdOne FROM tblPerson )
OR
jobCategoryId IN ( SELECT DISTINCT jobCategoryIdTwo FROM tblPerson)

And you can implement the above query by using a FieldComapreSetPredicate for each IN clause, ORed together.

hiya Walaa,

Ta for the quick reply.

You're correct with the query.I ran it and does exactly what I want.I'm having trouble with the llblGenPro code though.I originally used generic tables names to make my query clearer, this is the actual SQL that works for my tables.

SELECT  CategoryCode, Category 

FROM     BusinessCategory

WHERE 
CategoryCode IN ( SELECT DISTINCT PrimaryBusinessCategoryID FROM Business)
OR
CategoryCode IN ( SELECT DISTINCT SecondaryBusinessCategoryID FROM Business)

I have looked in the help and tried to create the llblGenPro equivalent:

 filter.Add(new FieldCompareSetPredicate(BusinessCategoryFields.CategoryCode, BusinessChangesFields.PrimaryBusinessCategoryId, SetOperator.In, null));

I "THINK" my issue is that I can't add the second field into the "SetField", that of "BusinessChangesFields.SecondaryBusinessCategoryId"

I'm not sure how I handle the "OR" clause, but I suppose I have to be able to add multiple fields to the ""SetField" first :-(

many thanks for any help.

yogi

Here you are:

filter.Add(new FieldCompareSetPredicate(BusinessCategoryFields.CategoryCode, BusinessChangesFields.PrimaryBusinessCategoryId, SetOperator.In, null));

filter.AddWithOr(new FieldCompareSetPredicate(BusinessCategoryFields.CategoryCode, BusinessChangesFields.SecondaryBusinessCategoryId, SetOperator.In, null));

hiya Walaa,

Thanks, unfortunately that does not bring back the same results as the query that I run against the SAME database.

The llblGenPro code returns less categories than the query.

I have tried 2 things to narrow down the problem:

1) I commmented out the following line:

filter.AddWithOr(new FieldCompareSetPredicate(BusinessCategoryFields.CategoryCode, BusinessChangesFields.SecondaryBusinessCategoryId, SetOperator.In, null));

RESULT: even less categories were returned.

2) I swapped the "field" and the "SetField"

filter.Add(new FieldCompareSetPredicate( BusinessChangesFields.PrimaryBusinessCategoryId, BusinessCategoryFields.CategoryCode, SetOperator.In, null));

RESULT: error<<The multi-part identifier "dbComapny.dbo.BusinessChanges.PrimaryBusinessCategoryID" could not be bound.>>

So, that tells me that my original arguments are in the correct position.

hmm, I'm struggling now

Any ideas?

many thanks,

yogi

Would you please examine and post the generated SQL query for the code I posted earlier? (ref: LLBLGen Pro manual "Using the generated code -> Troubleshooting and debugging")

hiya,

my mistake, I was using the BusinessChanges fields instead of the business fields.

Thanks for the help.

yogi

Walaa wrote:

The following should be your SQL query:

SELECT * FROM tblJobCategory
WHERE 
jobCategoryId IN ( SELECT DISTINCT jobCategoryIdOne FROM tblPerson )
OR
jobCategoryId IN ( SELECT DISTINCT jobCategoryIdTwo FROM tblPerson)

And you can implement the above query by using a FieldComapreSetPredicate for each IN clause, ORed together.

Hi Waala,

How's the performance on such a query?

I have a similar requirement:

Primary Table - Contacts (ContactID, Name, E-mail... etc.)

Secondary Table - ContactParameters (ContactID, ParameterName, ParameterValue). ContactID + ParameterName is the PK on the table.

I need to select all contacts with a set of parameters in ContactParameters. i.e.

All Contacts with Parameters A = 1 and B= 4.

mshe wrote:

I need to select all contacts with a set of parameters in ContactParameters. i.e.

All Contacts with Parameters A = 1 and B= 4.

Seems to me that your fetch can be done with a simple IRelationPredicateBucket.

daelmo wrote:

mshe wrote:

I need to select all contacts with a set of parameters in ContactParameters. i.e.

All Contacts with Parameters A = 1 and B= 4.

Seems to me that your fetch can be done with a simple IRelationPredicateBucket.

I don't think a simple join will return the proper values?

Basically the tables look like:

Contacts:

1(PK)    Joe
2    Bob
3    Another Guy

ContactParameters

1(FK)    Some Parameter         Some Value
1    Some Parameter2           Some Value2
2    Some Parameter        Some Value
2    Another Parameter         Some Value

etc etc

Hello,

you can create a relationpredicate bucket like this for your example:

dim rpb as new relationPredicateBucket
rpb.Relations.Add(ContactEntity.Relations.ContactParametersEntityUsingContactID)
rpb.PredicateExpression.Add((ContactParametersFields.Name = "A") AND (ContactParametersFields.Value = "1" ))
rpb.PredicateExpression.AddWithOr((ContactParametersFields.Name = "B") AND (ContactParametersFields.Value = "4") )

...

jbb wrote:

Hello,

you can create a relationpredicate bucket like this for your example:

dim rpb as new relationPredicateBucket
rpb.Relations.Add(ContactEntity.Relations.ContactParametersEntityUsingContactID)
rpb.PredicateExpression.Add((ContactParametersFields.Name = "A") AND (ContactParametersFields.Value = "1" ))
rpb.PredicateExpression.AddWithOr((ContactParametersFields.Name = "B") AND (ContactParametersFields.Value = "4") )

...

I'll give that a try ...

Thanks!

Actually I don't think that works...

If a Contact matches 1/2 the expression, then you'll get the result.

i.e

Contact 1 Name = 'A' and value = '1' Name ='B' and Value = '4'

Contact 2 Name ='B' and Value = '4' Name = 'C' and Value='2'

You'll get both records.

In this case, only record 1 should be returned.

Yep.. little typo error.... you should use _addWithAnd _instead.

dim rpb as new relationPredicateBucket
rpb.Relations.Add(ContactEntity.Relations.ContactParametersEntityUsingContactID)
rpb.PredicateExpression.Add((ContactParametersFields.Name = "A") AND (ContactParametersFields.Value = "1" ))
rpb.PredicateExpression.AddWithAnd((ContactParametersFields.Name = "B") AND (ContactParametersFields.Value = "4") )

daelmo wrote:

Yep.. little typo error.... you should use _addWithAnd _instead.

dim rpb as new relationPredicateBucket
rpb.Relations.Add(ContactEntity.Relations.ContactParametersEntityUsingContactID)
rpb.PredicateExpression.Add((ContactParametersFields.Name = "A") AND (ContactParametersFields.Value = "1" ))
rpb.PredicateExpression.AddWithAnd((ContactParametersFields.Name = "B") AND (ContactParametersFields.Value = "4") )

I don't think AND will work either ...

Because AND is will make the field exclusive.

Say you have two parameters ("State" and "ZIP")

So if you use AND - i.e. Name="State" AND Name="ZIP" that's exclusive - the field can't equal two values

Would the following be a correct query?

Assuming a Contact can't have 2 entries of the same parameter.

SELECT * 
FROM Conact
WHERE ID IN
(
SELECT ContactID
FROM ContactParameter
WHERE (ParameterName = "A" AND ParameterValue = 1)
OR (ParameterName = "B" AND ParameterValue = 2)
GROUP BY ContactID
HAVING Count(*) > 1
)

If the above is not the correct query, would you please post the correct one so we can help you convert it to LLBLGen Pro code?