search in several tables

[Jahappz]

Hi there people!

This is what i want to do.
I have a page there you can search...

SQL = "SELECT * FROM table WHERE file LIKE '%" & Request.Form("search") & "%' ORDER BY id"


the problem is that i have several tables and i dont want to specify every table in my select sentance. is there anyway to make it dynamic so that it adds "table,table1,table2" automatic?

like:

SQL = "SELECT * FROM table,table1,table2 WHERE file LIKE '%" & Request.Form("search") & "%' ORDER BY id"

 

[Sheco]

This is more of an SQL question than an ASP question.

Do all of the tables have the same exact fields? If so you can use the UNION operator with many databases.

Something like:
[tt]
SELECT * FROM table1
UNION
SELECT * FROM table2
[/tt]

This will ruturn one big resultset with duplicates removed but the columns much match up.

 

[Jahappz]

yes all tables have the exact same fields
... maybe i should post it SQL server forum?

 

[DotNetGnat]

Sheco, i dont think he is wanting to do that...

what i understood is that the field name file is in many tables and he wants to search for a paticular file name in all the tables...

he is looking for something like...information_schemas.columns

for example...

SELECT * FROM information_schemas.columns WHERE column_name='file'

-DNG

 

[Sheco]

Oh, I thought there was a column named "file" that appears in multiple tables and he wanted to return all the records from all the tables when "file" has a certain value.

... but yeah Jahappz, if you are using SQL Server then you'll get the best advice for structuring your SQL Command in that other forum

 

[Jahappz]

Like DotNetGnat i have several tables with the same field names in each table..

and i want to search for a specific word in all these tables....

anyone knows anything?

 

[Sheco]

Did you try this:
[tt]
SELECT * FROM table1 WHERE file LIKE '%blah"%' ORDER BY id
UNION
SELECT * FROM table2 WHERE file LIKE '%blah"%' ORDER BY id
UNION
SELECT * FROM table3 WHERE file LIKE '%blah"%' ORDER BY id
UNION
SELECT * FROM table4 WHERE file LIKE '%blah"%' ORDER BY id
UNION
SELECT * FROM table5 WHERE file LIKE '%blah"%' ORDER BY id
[/tt]

PS: I find it helpful to use the Query Analyzer to work out the exact syntax that I need for SQL Server queries... only after I get the syntax exactly right do I worry about putting it into my ASP pages.

 

[DotNetGnat]

i dont want to specify every table in my select sentance

thats what he mentioned in the original post...

-DNG

 

[Jahappz]

as i said i dont want to "hard code" the table names into the select sentence, i want it to
"read the table names from the database" and put the names into the select sentence and then search for the word...

 

[DotNetGnat]

Jahappz,

if i am not wrong there is no way to do a search on a column value...but you can do a search on a column name...

-DNG

 

[Jahappz]

well there is a few ways to get the table names
one is :

<%
Set objADOX = Server.CreateObject("ADOX.Catalog")
objADOX.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("db.mdb")
For Each objTable in objADOX.Tables
If objTable.Type = "TABLE" Then
Response.Write "<b><font color=#4a4a4a face=Verdana,Geneva,Arial,Helvetica size=1><u><a href=" & "visa.asp?tableid=" & objTable.Name &">" & objTable.Name & "</A></b></u><br>"
End If
Next
Set objADOX = Nothing
%>


that one gives you all tables without systemtables...
but if its possible to use this in anyway is more than i know..


i can specify how my tables look if helps:


table1

ID,Product,User
1,Microsoft,John
2,Unix,Tara

table2
ID,Product,User
1,HP,John
2,Canon,Eric


and if i search for "John"
i want to get two hits....

 

[DotNetGnat]

take a look at this thread...similar discussion is going on...

thread183-1106542

-DNG

 

[Jahappz]

DotNetGnat

the thing you wrote there:

SELECT * FROM information_schemas.columns WHERE column_name like 'Z%' AND column_value = 'grant'

that will only return the post where the user search for the column name right?

 

[DotNetGnat]

YES.

-DNG

 

[Jahappz]

how come it wont work with value?
thats just the way it is?

 

[DotNetGnat]

i too didnt knew that it wont work with value...but i think amrita suggested some other procedure to do this task...

did you read the amrita's post just after my post...

-DNG

 

[Jahappz]

Yes i read the post, i think the best options for me is to hard code the tables or rebuild the database structure....


Many thanks to you DotNetGnat for trying to help!

 

[DotNetGnat]

no problem, glad to be of help...

-DNG