asp .net / sql database / search function

[mattmerc]

Hi all,

I've searched around on this topic but haven't come up with anything
that matches exactly what I am trying to do. I have an ASP .net
application running over a SQL database. I need to allow users to do a
"free text" search on some of the SQL data. I'm thinking there must be
code out there to do this already. I don't want to reinvent the wheel,
and it doesn't help that I wouldn't know where to start reinventing
anyway...

 

[mattmerc]

Well, after more searching I have discoverd how to turn on the full
text indexing and use the sql CONTAINS and FREETEXT. I have run up on
another issue maybe someone can help with. This one is kind of hard to
explain, but here it goes.

The freetext search on my site should search various fields in
different tables. All tables are associated to the main table by
foreign key. When the search is complete a datagrid should show with
some preliminary info from the main table and a option to view a single
record or to export the datagrid to excel.

TABLE: incident PRIMARY KEY: incident_number
TABLE: person FOREIGN KEY: incident_number
TABLE: property FOREIGN KEY: incident_number
TABLE: attachment FOREIGN KEY: incident_number

My problem is that FREETEXT can only be performed on one table at a
time. So if I perform each query separately pulling the incident_number
from each query, how can I combine all of these incident numbers
(without duplicates) into my final datagrid?

Thanks all. I hope my description made some sense.

 

[mattmerc]

Well, looks like I may have solved my problem by using several SELECT
statements joined by a UNION. Anyone agree or disagree? Thanks.