using UNION to join tables

[Guest_imported]

I am trying to write a site search facility. As the information is in several tables I had the idea of using a UNION join. This works fine until I use a "WHERE LIKE '%keyword%'" clause.

Where am I going wrong? Any ideas? Many thanks

 

[rhyswilliams]

are you using access? if so why don't you just save your union query then when you open the recordset to search simply refence the union query and apply WHERE criteria to that.
Or in the union query statement you are currently(?) using to open the recordset, write WHERE statements for each SELECT statement?

I hope this solves your problem.

Please respond if this worked.

 

[Guest_imported]

I have written the following queries
This works:
SQLString = "SELECT text FROM department WHERE text = '" & Request.Form.Item("keyword&quot & "' UNION SELECT text FROM unit WHERE text = '" & Request.Form.Item("keyword&quot & "'"

yet this doesnot:
SQLString = "SELECT text FROM department WHERE text LIKE '%" & Request.Form.Item("keyword&quot & "%' UNION SELECT text FROM unit WHERE text LIKE '%" & Request.Form.Item("keyword&quot & "%'"

Set rs = conn.Execute("" & SQLString & "&quot
I am using access but generating my own SQL queries? Is that wot you meant by saving the union query?

 

[rhyswilliams]

firstly, i think that you need to use 3 % for like statements ie "like '%%%& Request.Form.Item("keyword&quot & %%%'"

but what i meant by saving the union query was actually creating a union query in access (you need to do this in SQL view in the editor) with no criteria set on it, saving this query as say UNIONQUERY, then whe you open the recordset on the asp page, use code like this:

Set rs = conn.Execute("SELECT UNIONQUERY.* from UNIONQUERY WHERE text like'%%%& Request.Form.Item("keyword") & %%%'")

this works on my server using an access97 database...

 

[Guest_imported]

Genius! It worked!

Not sure about the 3 %, but when I saved the query in Access it worked. I have not thought of writting queries actually in the access database before. Cool.

Genius, thanks very much for your time.