lucene-java-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Chris Lu" <chris...@gmail.com>
Subject Re: Searching for multiple criteria (accross 2 tables)
Date Fri, 15 Feb 2008 20:24:33 GMT
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 <tn_dinh@hotmail.com> 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: java-user-unsubscribe@lucene.apache.org
>  For additional commands, e-mail: java-user-help@lucene.apache.org
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: java-user-unsubscribe@lucene.apache.org
For additional commands, e-mail: java-user-help@lucene.apache.org


Mime
View raw message