Query for a Search Engine

Hi

I am trying to construct a search engine in asp using an access database.

I have 3 (possibly 1 or 2 more in the future) tables that need querying:

Images
ProductCode
HiRes

Products
ProductsURL
ProductCode
ProductTitle
ProductDescription
ProductSpec

Resources
resourcesURL
ProductCode
DataSheet
HandBook
Drivers1

I have tried a union all approach but it only returns the field HiRes:


SELECT HiRes
FROM Images
WHERE 
((ProductCode Like '*sftgsfghf*') or (HiRes Like '*sftgsfghf*'))
UNION
SELECT productsURL  
FROM Products
WHERE 
((ProductCode Like '*sftgsfghf*') or (ProductTitle Like '*sftgsfghf*') or (ProductDescription Like '*sftgsfghf*') or (ProductSpec Like '*sftgsfghf*'))
UNION
SELECT resourcesURL  
FROM Resources
WHERE 
((ProductCode Like '*sftgsfghf*') or (DataSheet Like '*sftgsfghf*') or (HandBook Like '*sftgsfghf*') or (Drivers1 Like '*sftgsfghf*'))

I need to return HiRes, productsURL, resourcesURL. Any ideas how I can achieve this or is there another approach?

actually, it does return everything you want, it’s just that in a union query, the result columns are named according to the first subselect in the union

let me change your query slightly for you…

SELECT 'images' AS source_table
     , HiRes AS found_column
  FROM Images
 WHERE ProductCode LIKE '*sftgsfghf*'
    OR HiRes LIKE '*sftgsfghf*'
UNION ALL
SELECT 'products'
     , productsURL  
  FROM Products
 WHERE ProductCode LIKE '*sftgsfghf*'
    OR ProductTitle LIKE '*sftgsfghf*'
    OR ProductDescription LIKE '*sftgsfghf*'
    OR ProductSpec LIKE '*sftgsfghf*'
UNION ALL
SELECT 'resources
     , resourcesURL  
 FROM Resources
 WHERE ProductCode LIKE '*sftgsfghf*'
    OR DataSheet LIKE '*sftgsfghf*'
    OR HandBook LIKE '*sftgsfghf*'
    OR Drivers1 LIKE '*sftgsfghf*'

i added a new column to identify which table the row was found in, and changed UNION to UNION ALL (to avoid an unnecessary and expensive sort)

i also removed those stupid unnecessary parentheses that access loves so much, but it’s gonna put them back when you save the query

:slight_smile: