database trouble

i have to design a search application. the data i have to check for is stored in two different oracle databases. after i get parts of the complete information from each database..i need to map them using the id of each record. this info is present in a third database.

ie. 1st db has: name and db1id
2nd db has: address and db2id
3rd db has: db1id mapped to the corresponding db2id

search based on name or address (or both) values entered by user.

looping of queries is not working. can someone tell me a way of doin this ??

thanks,
Regards.

Are these in different database tables or are these actually in separate database schemas?

Assuming separate tables, you'll want to use a join to relate the tables together, something like this:

select n.name, a.address
from name n, address a, name_address na
where
(n.name = '#form.nameSearchField#'
or a.address = '#form.addressSearchField#')
and n.nameID = na.nameID
and na.addressID = a.addressID

hi there
thanks a lot for the solution !! but the problem is that the data is in 3 separate databases and not just 3 tables.

cud u please tell me a way out ???

and i have a problem with passing a set of results of a query from one page to another in the form of a list . how do i go about it ???

hi there...
its me again !! im new to cf and really dont know much abt it !!
i jus wanted to know if using trim on a variable with a null value can cause trouble ??
i have a search applicn where the data cud come from simple search (name based) or an advanced search with lots of fields. so some parameters cud be defined or not depending on which type of search it is

so cud u please tell me how i shud go about with the query ???? am really gettin frustrated with it !!!

many thanks in advance !!!

CF treats null values as empty strings. So if you use trim on an empty string, you get an empty string. It doesn't do anything.

First, if the related data are in different schemas, you probably need to rethink your database design.

That said, you can prefix any table or field name with the name of the schema. So if you can do "schema1.table1" and "schema2.table2" in your FROM clause. This is how it works in Oracle.

Now if your data is actually in two totally separate databases, with totally separate datasource names, you can't do this in SQL. You'll have to query one datasource, then query the other datasource, and then write code that puts the data together, possibly using query of queries or the CFML query manipulation functions.