Sorry, sent the previous draft email by mistake. Here is the correct one.
Sounds a typical SQL pivot problem.
select Id, SIN, data.*
from IdCard, (SELECT
ID
MAX(CASE WHEN name = 'Fname' THEN Value END) AS Fname,
MAX(CASE WHEN name = 'Lname' THEN Value END) AS Lname,
MAX(CASE WHEN name = 'Age' THEN Value END) AS Age,
MAX(CASE WHEN name = 'Country' THEN Value END) AS Country
FROM
DATA_Table
GROUP BY
ID
) data
To speed things up, you can split the SQLs into 2 for better performance.
This is how DBSight does this. You can write your own SQLs, but
generally it's the same methods.
--
Chris Lu
-------------------------
Instant Scalable Full-Text Search On Any Database/Application
site: http://www.dbsight.net
demo: http://search.dbsight.com
Lucene Database Search in 3 minutes:
http://wiki.dbsight.com/index.php?title=Create_Lucene_Database_Search_in_3_minutes
DBSight customer, a shopping comparison site, (anonymous per request)
got 2.6 Million Euro funding!
On Fri, Feb 15, 2008 at 11:27 AM, lmctndi <[EMAIL PROTECTED]> wrote:
>
> Thanks for your reply.
>
> Your idea prompts more questions:
>
>
> I understand what you are saying but don't know how to implement it. How do
> you go about joining all rows of all the tables belonging to one person and
>
>
> to index them so that I can actually use
> "+Fname:john +County:USA" as a query?
>
>
> Erick Erickson wrote:
> >
> > To expand a bit on Chris's first point: Take off your DB hat and put on
> > your search hat <G>. It sounds like you have simply moved your database
> > tables into Lucene and want to search across them. My rule is that
> > whenever you find yourself trying to make Lucene act like a DB, you
> > need to pause and reflect on your design.....
> >
> > So, from your example, you select all the data relating to id 1 from
> > *all* your tables, and index that as a single document in Lucene. Very
> > simplistically, your document for ID 1 has the fields
> > Fname, Lname, Age, Country, and SIN.
> >
> > Your query is now very simple,
> > +Fname:john +County:USA
> >
> > and to get the related SIN, you iterate over your hits
> > and extract the SIN from each hit.
> >
> > If I understand your problem, that is <G>.
> >
> > In general, the strategy is to de-normalize your information
> > when you build your index....
> >
> > Best
> > Erick
> >
>
> --
> View this message in context:
> http://www.nabble.com/Searching-for-multiple-criteria-%28accross-2-tables%29-tp15502657p15508362.html
> Sent from the Lucene - Java Users mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
