How to do search on two or three data tables at once!

Hi
Looking at the vb.net code below how can I modify the code so that when using SQL Servers
Full Text Search it will search two or three data tables at once Jobs Jobs2 and jobs3 all
tables are in the same database aspnetjobs and share the same job_id column. I can perform a search on one table but not two. Please look
at the vb.net code below and help if you can.

thanks!

sub button_click(s as object, e as eventargs)

Dim conaspnetjobs as sqlconnection
Dim strsearch as string
Dim cmdsearch as sqlcommand
Dim dtrsearch as sqldatareader

conaspnetjobs = new sqlconnection ( "server=mark\mark;uid=sa;pwd=clr_fcl;database=aspnetjobs" )

strsearch ="select job_fulldesc from Jobs, jobs2 where freetext( job_fulldesc, @.searchphrase )"
cmdsearch = new sqlcommand( strsearch, conaspnetjobs )
cmdsearch.parameters.add( "@.searchphrase", txtsearchphrase.text )

conaspnetjobs.open()
dtrsearch = cmdsearch.executereader()

while dtrsearch.read
lblresults.text &= "<li><br>" & dtrsearch( "job_fulldesc" )
end while

conaspnetjobs.close
end subHi,

Can't you simply join the other job tables in you query?

select job_fulldesc from Jobs a join jobs2 b on a.job_id = b.job_id where freetext( job_fulldesc, @.searchphrase )"

:-D

JB|||I think you need a UNION ALL. This will search each of the tables and combine the results into one resultset. I added a "source" column so you'd know which table the match come from; you may not need this.

"select job_fulldesc from Jobs, source="Jobs" where freetext( job_fulldesc, @.searchphrase )
UNION ALL
select job_fulldesc from Jobs2, source="Jobs2" where freetext( job_fulldesc, @.searchphrase )
UNION ALL
select job_fulldesc from Jobs3, source="Jobs3" where freetext( job_fulldesc, @.searchphrase )"

Terri|||Hi
I tryed the UNION ALL as above and recieved error message "UNION" is not declared!

please help!|||Something ran amok with the code I suggested. No wonder it didn't work. This is what I meant:

"select job_fulldesc, source='Jobs' from Jobs where freetext( job_fulldesc, @.searchphrase )
UNION ALL
select job_fulldesc, source='Jobs2' from Jobs2 where freetext( job_fulldesc, @.searchphrase )
UNION ALL
select job_fulldesc, source='Jobs3' from Jobs3 where freetext( job_fulldesc, @.searchphrase )"

I tried this out by creating a duplicate of Products called Products2 on Northwind, and then creating a full text index on the ProductName field. Then I ran this successfully:

DECLARE @.searchphrase varchar(20)
SET @.searchphrase = 'Ale'
SELECT productname, source='Jobs' FROM products WHERE FREETEXT(productname, @.searchphrase )
UNION ALL
SELECT productname, source='Jobs2' FROM products2 WHERE FREETEXT(productname, @.searchphrase )
UNION ALL
SELECT productname, source='Jobs3' FROM products WHERE FREETEXT(productname, @.searchphrase )

|||I get this error message "statement is not valid inside a method"

Declare @.sreachphrase varchar(20 )

Please Help!|||That bottom block of code I posted was strictly for Query Analyzer use as proof of concept, not to copy and paste into your ASP.NET page.

This block of code is what should work for you:

strsearch ="select job_fulldesc, source='Jobs' from Jobs where freetext( job_fulldesc, @.searchphrase ) UNION ALL select job_fulldesc, source='Jobs2' from Jobs2 where freetext( job_fulldesc, @.searchphrase ) UNION ALL select job_fulldesc, source='Jobs3' from Jobs3 where freetext( job_fulldesc, @.searchphrase )"

Terri