I have three tables in my db: 

1)Category 

  ID(PK) 

  Name 



2)Company 

  ID(PK) 

  Name 

  Business 

  CategoryID(FK) 'from table Category 



3)Topics 

  ID(PK) 

  Name 

  Description 

  CompanyID(FK) 'from table Company 



If I "SEARCH" the string from only one table I do like this: 



'To searching for a string from the tblCompany 

<% 

If Request.Form("Name").Count <> 0 Then 

  Name = QuoteReplace(Request.Form("Name")) 

  Business = QuoteReplace(Request.Form("Name")) 

End If 

' Name is a text box where user enter the word they are searching for 



function Add_OR() 

     If temp_sql <> "" Then temp_sql = temp_sql & " OR " 

End Function 





If Name <>"" Then 

    temp_sql = temp_sql & "[Name] LIKE '%" & Name & "%'" 

End If 



If (Business <> "") Then 

    Add_OR() 

    temp_sql = temp_sql & "[Business] LIKE '%" & Business & "%'" 

End If 



If temp_sql <> "" Then 

     sql = "SELECT * FROM [Company]" 

    sql = sql & " WHERE (" & temp_sql & ")" 

    sql = sql & " ORDER BY CatID, Name, Business;" 

Else 

    sql= "Select ID From Company where ID=0" 

End If 





The Problem is, if I want to search the string enter by user from the 

table Category(Name) and Topics(Name, Description). I don't know how to 

build the query, but I change my code like this. 



<% 

If Request.Form("Name").Count <> 0 Then 

  Name = QuoteReplace(Request.Form("Name")) 

  Business = QuoteReplace(Request.Form("Name")) 

  Topics= QuoteReplace(Request.Form("Name")) 

  Description= QuoteReplace(Request.Form("Name")) 

  Category= QuoteReplace(Request.Form("Name")) 

End If 



function Add_OR() 

     If temp_sql <> "" Then temp_sql = temp_sql & " OR " 

End Function 





If Name <>"" Then 

    temp_sql = temp_sql & "[Name] LIKE '%" & Name & "%'" 

End If 



If (Business <> "") Then 

    Add_OR() 

    temp_sql = temp_sql & "[Business] LIKE '%" & Business & "%'" 

End If 



If (Topics<> "") Then 

    Add_OR() 

    temp_sql = temp_sql & "[Name] LIKE '%" & Business & "%'" 

End If 

'Is it correct if I just put name because table company and category also 

have "name" fields. 



If (Description<> "") Then 

    Add_OR() 

    temp_sql = temp_sql & "[Description] LIKE '%" & Description& "%'" 

End If 



If (Category<> "") Then 

    Add_OR() 

    temp_sql = temp_sql & "[Name] LIKE '%" & Category& "%'" 

End If 

%> 



Can someone help me, how to create the SELECT statement using join based 

on my problem. Thanks in advance 

Dear Shariman,



I think i have solution to Your Problem.the sample of your code consists 

of three tables .why dont you create three different recordsets for each 

table and then try to build the Query from there on after stioring into a 

variable.





eg.



set ca=server.createobject("adodb.recordset")

sql=select name from category

ca.open sql,c

'c is the variable which is used for opening the connection

("adodb.connection")



set co=server.createobject("adodb.recordset")

sql1=select name from company

t.open sql1,c



set t=server.createobject("adodb.recordset")

sql2=select name from topics

t.open sql2,c



now use a variable for storing the required values and used it in if 

condition .

eg.

cat=ca(0)

com=co(0)

t=t(0)





regards,

vishal





































> I have three tables in my db: 

> 1)Category 

>   ID(PK) 

>   Name 

> 

> 2)Company 

>   ID(PK) 

>   Name 

>   Business 

>   CategoryID(FK) 'from table Category 

> 

> 3)Topics 

>   ID(PK) 

>   Name 

>   Description 

>   CompanyID(FK) 'from table Company 

> 

> If I "SEARCH" the string from only one table I do like this: 

> 

> 'To searching for a string from the tblCompany 

> <% 

> If Request.Form("Name").Count <> 0 Then 

>   Name = QuoteReplace(Request.Form("Name")) 

>   Business = QuoteReplace(Request.Form("Name")) 

> End If 

> ' Name is a text box where user enter the word they are searching for 

> 

> function Add_OR() 

>      If temp_sql <> "" Then temp_sql = temp_sql & " OR " 

> End Function 

> 

> 

> If Name <>"" Then 

>     temp_sql = temp_sql & "[Name] LIKE '%" & Name & "%'" 

> End If 

> 

> If (Business <> "") Then 

>     Add_OR() 

>     temp_sql = temp_sql & "[Business] LIKE '%" & Business & "%'" 

> End If 

> 

> If temp_sql <> "" Then 

>      sql = "SELECT * FROM [Company]" 

>     sql = sql & " WHERE (" & temp_sql & ")" 

>     sql = sql & " ORDER BY CatID, Name, Business;" 

> Else 

>     sql= "Select ID From Company where ID=0" 

> End If 

> 

> 

> The Problem is, if I want to search the string enter by user from the 

> table Category(Name) and Topics(Name, Description). I don't know how to 

> build the query, but I change my code like this. 

> 

> <% 

> If Request.Form("Name").Count <> 0 Then 

>   Name = QuoteReplace(Request.Form("Name")) 

>   Business = QuoteReplace(Request.Form("Name")) 

>   Topics= QuoteReplace(Request.Form("Name")) 

>   Description= QuoteReplace(Request.Form("Name")) 

>   Category= QuoteReplace(Request.Form("Name")) 

> End If 

> 

> function Add_OR() 

>      If temp_sql <> "" Then temp_sql = temp_sql & " OR " 

> End Function 

> 

> 

> If Name <>"" Then 

>     temp_sql = temp_sql & "[Name] LIKE '%" & Name & "%'" 

> End If 

> 

> If (Business <> "") Then 

>     Add_OR() 

>     temp_sql = temp_sql & "[Business] LIKE '%" & Business & "%'" 

> End If 

> 

> If (Topics<> "") Then 

>     Add_OR() 

>     temp_sql = temp_sql & "[Name] LIKE '%" & Business & "%'" 

> End If 

> 'Is it correct if I just put name because table company and category 

also 

> have "name" fields. 

> 

> If (Description<> "") Then 

>     Add_OR() 

>     temp_sql = temp_sql & "[Description] LIKE '%" & Description& "%'" 

> End If 

> 

> If (Category<> "") Then 

>     Add_OR() 

>     temp_sql = temp_sql & "[Name] LIKE '%" & Category& "%'" 

> End If 

> %> 

> 

> Can someone help me, how to create the SELECT statement using join based 

> on my problem. Thanks in advance