Search on multiple key/value fields...

Posts   
 
    
hlesesne avatar
hlesesne
User
Posts: 47
Joined: 22-Jul-2004
# Posted on: 24-Aug-2004 01:45:15   

I have a lead table and then a related table that stores lead information as a keyfield, keyvalue pair... I.e. Lead --> LeadInfo

Lead would have a LeadId and an add date, LeadID, LeadAddDate 1, 1/1/2004

and LeadInfo may contain any number of fields, for example LeadID, LeadInfoField, LeadInfoValue 1, FirstName, Hal 1, LastName, Lesesne 1, Address, 555 Anytown 1, Phone, 828-555-5555

Would it be possible to create a predicate expression to represent a search for

FirstName = 'Hal' AND LastName = 'Lesesne'

in just a T-SQL statement, I suppose I would do something like JOINing the table to search on for each field I need to check...

I.e.

select * from tblLead A JOIN tblLeadInfo B ON A.LeadID = B.LeadID JOIN tblLeadInfo C ON A.LeadID = C.LeadID WHERE B.LeadInfoFIeld = 'FirstName' and B.LeadInfoValue = 'Hal' AND C.LeadInfoField = 'LastName' and C.LeadInfoValue = 'Lesesne'

Would I simply add additional relations to my getmulti? That was my first thought, but couldn't quite come up with how to formulate it.

This is sort of what I was looking for in http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=1262 - but I am actually not storing the field index, I am using an entirely different table, so I thought I would try to clarify it in this post.

If I just add multiple predicates, woudn't that

Thanks again for all the help and the awsome support.

Best regards,

Hal Lesesne

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 24-Aug-2004 10:33:04   

As you have to join the table twice, you run into a limitation: this is not yet supported. The runtime library updates which go into beta today will solve this.

Frans Bouma | Lead developer LLBLGen Pro
hlesesne avatar
hlesesne
User
Posts: 47
Joined: 22-Jul-2004
# Posted on: 10-Sep-2004 21:04:28   

Otis wrote:

As you have to join the table twice, you run into a limitation: this is not yet supported. The runtime library updates which go into beta today will solve this.

Is this something that should work now in the latest release? Thanks for all the help.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 10-Sep-2004 21:22:41   

hlesesne wrote:

Otis wrote:

As you have to join the table twice, you run into a limitation: this is not yet supported. The runtime library updates which go into beta today will solve this.

Is this something that should work now in the latest release? Thanks for all the help.

Yes, this should work with the runtime library beta, v1.0.2004.1 of the runtime libraries and accompanying templates. Check the enclosed unit test sourcecode for ideas how to use it (check the Northwind multifetch tests.)

Frans Bouma | Lead developer LLBLGen Pro