I have been using ASP, VBscript and an MS-Access 2K DB.

I have a script that produces SQL that should return search results on takeaways in a given town.

However the other day during testing I discovered the QUERY IS TOO COMPLEX error message.

I use nested queries two levels deep and a loop that builds UNION SELECTs into the heart of the SQL.

(Small note it now builds UNION ALL SELECTs)

The SQL inquestion is given below.

So now of course I need to think about a more powerfull database system or a better method for searching.

Your feedback is apprisicated.

(the app is back working as half the dictionary of fields have been commented out.)

The string we threw at it was "pizza alternative jacket potato fillg" which was caused by a cut and past error in earlier testing.

Code:
 
Select * from ListLevel 
INNER JOIN 
(SELECT EntityName, Entity.EntityID, listlevel, [Short Description], add1, add2, town, county, fullcode, Region.Region, PrimaryEmail, [Company URL], BranchPhone,  Sum(Rel) as SumOfRel FROM 
(SELECT *, 10 as rel FROM [Q_Pages] where EntityName like '%pizza alternative jacket potato fillg%' 
Union SELECT *, 1 as rel FROM [Q_Pages] where EntityName like '%pizza%'  
Union SELECT *, 1 as rel FROM [Q_Pages] where EntityName like '%alternative%'  
Union SELECT *, 1 as rel FROM [Q_Pages] where EntityName like '%jacket%'  
Union SELECT *, 1 as rel FROM [Q_Pages] where EntityName like '%potato%'  
Union SELECT *, 1 as rel FROM [Q_Pages] where EntityName like '%fillg%'  
Union SELECT *, 2 as rel FROM [Q_Pages] where [Short Description] like '%pizza%'  
Union SELECT *, 2 as rel FROM [Q_Pages] where [Short Description] like '%alternative%'  
Union SELECT *, 2 as rel FROM [Q_Pages] where [Short Description] like '%jacket%'  
Union SELECT *, 2 as rel FROM [Q_Pages] where [Short Description] like '%potato%'  
Union SELECT *, 2 as rel FROM [Q_Pages] where [Short Description] like '%fillg%'  
Union SELECT *, 1 as rel FROM [Q_Pages] where Add1 like '%pizza%'  
Union SELECT *, 1 as rel FROM [Q_Pages] where Add1 like '%alternative%'  
Union SELECT *, 1 as rel FROM [Q_Pages] where Add1 like '%jacket%'  
Union SELECT *, 1 as rel FROM [Q_Pages] where Add1 like '%potato%'  
Union SELECT *, 1 as rel FROM [Q_Pages] where Add1 like '%fillg%'  
Union SELECT *, 1 as rel FROM [Q_Pages] where Add2 like '%pizza%'  
Union SELECT *, 1 as rel FROM [Q_Pages] where Add2 like '%alternative%'  
Union SELECT *, 1 as rel FROM [Q_Pages] where Add2 like '%jacket%'  
Union SELECT *, 1 as rel FROM [Q_Pages] where Add2 like '%potato%'  
Union SELECT *, 1 as rel FROM [Q_Pages] where Add2 like '%fillg%'  
Union SELECT *, 2 as rel FROM [Q_Pages] where Town like '%pizza%'  
Union SELECT *, 2 as rel FROM [Q_Pages] where Town like '%alternative%'  
Union SELECT *, 2 as rel FROM [Q_Pages] where Town like '%jacket%'  
Union SELECT *, 2 as rel FROM [Q_Pages] where Town like '%potato%'  
Union SELECT *, 2 as rel FROM [Q_Pages] where Town like '%fillg%'  
Union SELECT *, 2 as rel FROM [Q_Pages] where County like '%pizza%'  
Union SELECT *, 2 as rel FROM [Q_Pages] where County like '%alternative%'  
Union SELECT *, 2 as rel FROM [Q_Pages] where County like '%jacket%'  
Union SELECT *, 2 as rel FROM [Q_Pages] where County like '%potato%'  
Union SELECT *, 2 as rel FROM [Q_Pages] where County like '%fillg%'  
Union SELECT *, 4 as rel FROM [Q_Pages] where BranchPhone like '%pizza%'  
Union SELECT *, 4 as rel FROM [Q_Pages] where BranchPhone like '%alternative%'  
Union SELECT *, 4 as rel FROM [Q_Pages] where BranchPhone like '%jacket%'  
Union SELECT *, 4 as rel FROM [Q_Pages] where BranchPhone like '%potato%'  
Union SELECT *, 4 as rel FROM [Q_Pages] where BranchPhone like '%fillg%'  
Union SELECT *, 5 as rel FROM [Q_Pages] where FullCode like '%pizza%'  
Union SELECT *, 5 as rel FROM [Q_Pages] where FullCode like '%alternative%'  
Union SELECT *, 5 as rel FROM [Q_Pages] where FullCode like '%jacket%'  
Union SELECT *, 5 as rel FROM [Q_Pages] where FullCode like '%potato%'  
Union SELECT *, 5 as rel FROM [Q_Pages] where FullCode like '%fillg%'  
Union SELECT *, 6 as rel FROM [Q_Pages] where Region.Region like '%pizza%'  
Union SELECT *, 6 as rel FROM [Q_Pages] where Region.Region like '%alternative%' 
Union SELECT *, 6 as rel FROM [Q_Pages] where Region.Region like '%jacket%' 
 Union SELECT *, 6 as rel FROM [Q_Pages] where Region.Region like '%potato%'  
Union SELECT *, 6 as rel FROM [Q_Pages] where Region.Region like '%fillg%'  
) T1 
Group By 
EntityName, Entity.EntityID, listlevel, [Short Description], add1, add2, town, county, fullcode, Region.Region, PrimaryEmail, [Company URL], BranchPhone  Order by Sum(rel)
) T2 ON T2.T1.ListLevel = ListLevel.ListLevelID ;