We help IT Professionals succeed at work.

sql help ( searching multiple tables )

inthe
inthe asked
on
hello ,

table1
table1 field1  (text   <-search me)
table1 field2  (memo   <-- and me)
table1 field3  (memo   <-forget me)

i have many(30 ish) access tables of same structure they arent linked together/indexed/keyfielded)? at all if it matters ,i need to search the two fields from each table( one field is memo type other is text type) and return them to dbgrid


field names are same as tablename + space then another word and im reading them from combobox which was filled via gettablenames;
the following statement works fine on the text field
AdoQuery1.SQL.Text :=
'SELECT * FROM ['+Combobox1.text+'] WHERE['+
combobox1.text+' title] LIKE "'+Edit1.Text+'%"'

the sql it returns is
SELECT * FROM [testtable] WHERE[testtable field1] LIKE "shell%"
ok great so filling field1 with api calls it finds shellexecute,shellexecuteex etc..

now im stuck
first problem is it doesnt work on the memo field ,i think i need to use "in" statement for that field.
and main prob how to do the search over both fields from all the tables in combobox.

i dont know where to go from here i dont know much sql so maybe i should be using "union all" or adding each found record to a completely new table using "insert into" ?do i need to set keyfields for many tables to make it easier?
i really need a complete sql(tested ideally) answer so plenty of points for sql gurus..oh if there more than one way please say what is better and why ..
(hasnt someone made a tool for doing this yet ;-)
Back later
Regards Barry
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2004
Commented:
normally you can't search in memofields :-(
(as i said you should rework your database-design)

CERTIFIED EXPERT
Top Expert 2004

Commented:
a construct could be

create table newtablename
as (
select fieldList from tablename1
union all
select fieldlist from tablename2 ....
)
where searchcondition

not applyable to all database-systems

meikl ;-)
CERTIFIED EXPERT
Top Expert 2004

Commented:
or, to minimze temporary subsets


create table newtablename
as (
select fieldList from tablename1 where searchcondition
union all
select fieldlist from tablename2 where searchcondition
union all
....
)



CERTIFIED EXPERT
Top Expert 2004

Commented:
hmm, examined a bit the access jet-sql help
(guessing u use access)

the syntax should be

select fieldlist into newtablename from
select fieldList from tablename1 where searchcondition
union all
select fieldlist from tablename2 where searchcondition
union all
....

meikl ;-)

Author

Commented:
>>can't search in memofields

damn thats got me good there :(
what field type can i use in access to store the infos to search it?
thats weird though as i wouldve though thats where all the good stuff was kept .
though at least the data is now nearly fully in access i can easily switch/move it around.


this is  familiar someone else showed me the :
select fieldlist from tablename2 where searchcondition
union all
may have been epsylon but i couldnt remember how to use it.


im actually thinking of completely switching to mysql and just having this as a backup/base store but id still like to finish it
 (i hate leaving stuff half done+good learning exersize)

can you give me a design plan for the three fields
is using tablename and fieldname the same as i did above a good idea or bad(i found it easier to reference in code)

Author

Commented:
double darn, your right ,i just did a test and it only searches the first line of the memo fields which is no good at all.

Author

Commented:
just to note if you dont have access the fields types i see are
memo
text
datetime
autonumber
lookup <--lookup another table
yes/no
hyperlink
oleobject

the text field in access is limited to 255 chars.
some of my codes are 500 chars +

heh findfirst/findnext on bunch of text files would be easier :-)
as i am already doing the same in mysql for another db and i know it works in mysql do you think i should forget perfecting the access copy and just add more keywords to the text field so it at least partially works (if its only for backup in case of data loss ,single table searching wont matter i suppose )
my mind is like the wind today ;)

Commented:
I never had a problem searching a memo.
I was using ADO in ASP though.

There is a problem in ASP + ADO which might apply, when selecting you must ask for the memo fields first

select memo1, memo2, field1, field2
from mytable where memo1 contains 'hello'
union all
select memo1, memo2, field1, field2
from mytable2 where memo1 contains 'hello';

I am not 100% about "contains", MS probably decided to change the keyword :-)

Anyway, you could try !

Pete
====
http://www.HowToDoThings.com (Delphi articles)
http://www.Stuckindoors.com/delphi (Open source graphics, sound, etc)

Author

Commented:
db is changed etc and working nicely i did change to mysql using php,i sent you a mail.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.