Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsGeneralPHPASPPerlColdFusionFlashHTML, CSS, ScriptsBrowsers

Webmaster Forum / ColdFusion / Advanced Techniques / September 2007


Tip: Looking for answers? Try searching our database.

How create results with an Outer Join using 2 different databases

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
JoyRose - 05 Sep 2007 20:26 GMT
:rose;

SUBJECT: How create results with an Outer Join using 2 different databases

ISSUE:

I'm working with existing functionality, so I need to work this code in
somehow or rewrite a significant amount of code.

I am using tables that come from different datasources.
I can't use the outer join with QofQ, right? I need to use an outer join to
combine two result sets that don't have same

number of matches.
 
GOAL:

I want to end up with final results so that any record from qdocSourceSort
with a fac_ID value has its sourceTitle field

populated with the sourceTitle created in qqFacTitle.
(qdocSourceSort already has the fac_id NULL value records populated with
SourceTitle values.)

My main objective is to sort all these records by SourceTitle!

CODE:

The two tables I need to combine are qdocSourceSort and qqFacTitle below.
(qqSourceFacNo (below) is an intermediate step.)

I want all these records:
<cfquery name="qdocSourceSort" datasource="#request.peds#">
select *
from PEdocs, Sources
WHERE deleted IS NULL
and UPPER(docLetter) = <CFQUERYPARAM value="#attributes.letter#"
cfsqltype="CF_SQL_CHAR" maxlength="1">
and fk_sourceID = sourceID                           
</cfquery>

I want to combine the results above with the last query of the two below.

<cfquery dbtype="query" name="qqSourceFacNo">
select sourceFacID
from qdocSourceSort
where sourceFacID IS NOT NULL                            
</cfquery>       
   
                           
<cfquery  name="qqFacTitle" datasource="#request.generalds#">
select fac_ID, fac_Facility AS sourceTitle
from IHSFacility
where fac_ID IN (#ValueList(qqSourceFacNo.sourceFacID)#)
</cfquery>

qqFacTitle.fac_ID would match some of the qdocSourceSort.fac_ID values. The
rest of the qdocSourceSort.fac_ID values would

be NULL.

Also numerous qdocSourceSort.fac_ID values could be the same number, so each
result from qqFacTitle.fac_ID needs to be

matched as many times as it is referenced in qdocSourceSort.fac_ID values.

qdocSourceSort has a column SourceTitle, so each result of
qqFacTitle.SourceTitle needs to be merged into the SourceTitle

fields in a qdocSourceSort record wherever
qqFacTitle.fac_ID matches qdocSourceSort.fac_ID.

Can anybody help me?!?!?

Thanks.
Reply to this Message
Ian Skinner - 05 Sep 2007 21:05 GMT
The easiest way to do this would be if the databases could talk to each
other directly.  Most high level DBMS have a way to make a connection to
another database so that one can combine the data from both databases in
a single query.

For MSSQL the syntax is databaseName.databaseOwner.table so if two MSSQL
databases can talk to each other one can do stuff like this.

SELECT aField, bField, cField
FROM oneDatabase.dbo.aTable LEFT JOIN twoDatabase.dbo.bTable ON
aTable.aColumn = bTable.aColumn

For Oracle on sets up 'remote database links' in the DBMS that are then
used on the SQL statements.

SELECT aField, bField, cField
FROM aTable, bTable@remoteLinkName
WHERE aTable.aColumn(+) = bTable.aColumn

If one is unable to do this at the database level then the next option I
see is an ugly looping solution.  Loop over one record set and build a
new query data structure combining the data from the other record set as
relevant.  I can see ways to do this by looping over a QofQ or by
creating an associative structure of one record set and reference the
keys to make the connection to the first record set results.  I would
probably try the latter solution first.
Reply to this Message
JoyRose - 05 Sep 2007 21:59 GMT
Thank you for your quick response.

How would I set up the query tag? Don't I need to reference a datasource for
each database?
For example, one table has a datasource like this:
  <cfquery  name="qqFacTitle" datasource="#request.generalds#">

The other database has a datasource like this:
<cfquery name="qdocSourceSort" datasource="#request.peds#">

Thanks again.
I really need to get this done and move on!
I appreciate your help.
Reply to this Message
Ian Skinner - 05 Sep 2007 22:58 GMT
How would I set up the query tag? Don't I need to reference a
datasource for
each database?
 For example, one table has a datasource like this:
   <cfquery  name="qqFacTitle" datasource="#request.generalds#">

 The other database has a datasource like this:
 <cfquery name="qdocSourceSort" datasource="#request.peds#">

If they databases are set up to do this connection themselves then you
would just use one datasource to connect to a database and that database
can connect to the other.

<cfquery ... dataSource="dsnToSmartDBMS">
  SELECT FIELDS
  FROM TablesInBothDatabases
</cfquery>
Reply to this Message
Dan Bracuk - 05 Sep 2007 22:01 GMT
I don't think you need to loop.  I think you can do it all with Query of Query
and ValueLists.  Something like

1st q of q
join the two queries on the common field.

2nd q of q
select from query1 where somefield not in (valuelist from query2)

3rd q of q
maybe a union of the first two.  or maybe you need a couple more.
Reply to this Message
cf_dev2 - 05 Sep 2007 22:29 GMT
> How would I set up the query tag?

The process to connect 2 database servers, and the query syntax, depends on
which db you're using.  If you mean querying two different db's (of the same
type) that reside on the same server, you may be able to do this already using
the syntax Ian Skinner posted.

So the question is what are you trying to query? Two different database types,
two databases on the same server, etc?
Reply to this Message
JoyRose - 06 Sep 2007 13:43 GMT
I'm using one SLQ server. I have two different databases that I need to query
tables from.

Ian, are you suggesting I make the datasource from one database and just refer
to both databases in the FROM clause, like: db1.table1, db2.table2?

Another issue is that I need a result from the first query (which queries two
tables) and use that result to join with a second query against a second
database.
Is there a way to combine the first query in a LEFT JOIN with the second query?
Please refer to the code in my original post.

Thanks, all.
Reply to this Message
Dan Bracuk - 06 Sep 2007 15:04 GMT
[q][i]Originally posted by: [b][b]JoyRose[/b][/b][/i]
Ian, are you suggesting I make the datasource from one database and just refer
to both databases in the FROM clause, like: db1.table1, db2.table2?
[/q]
That's what he means.  A pre-requisite is that the appropriate permissions
have to be set at the database level.
Reply to this Message
Ian Skinner - 06 Sep 2007 15:20 GMT
"[q][i]Originally posted by: [b][b]JoyRose[/b][/b][/i]
Ian, are you suggesting I make the datasource from one database and just
refer to both databases in the FROM clause, like: db1.table1,
db2.table2?[/q]

That's what he means.  A pre-requisite is that the appropriate
permissions have to be set at the database level."

As well as the proper database configuration, the proper syntax to refer
to another database in MSSQL is databaseName.owner.table, so your
example would more properly be db1.dbo.table1, db2.dbo.table2, making
assumptions about database names, owner names and table names..

Different DBMS do this differently, so check your documentation and|or DBA.
Reply to this Message
cf_dev2 - 06 Sep 2007 15:44 GMT
[i]Originally posted by: [b][b]JoyRose[/b][/b][/i]
> are you suggesting I make the datasource from one database and just refer to
both
> databases in the FROM clause, like: db1.table1, db2.table2?

Yes, but don't forget the "owner". The syntax in Ian Skinner's example has 3
parts:    databaseName.owner.tableName

> 2. Is there a way to combine the first query in a LEFT JOIN with the second
query?

Yes.  Its just a regular LEFT JOIN except you'll use the 3 part name for the
tables.  As Dan Bracuk mentioned the correct permissions must be established
for this to work.
 
SELECT  t1.ColumnA, t1.ColumnB
FROM    databaseName1.dbo.table1 AS t1
    INNER JOIN databaseName1.dbo.table2 AS t2
            ON t1.Column = t2.Column
    LEFT JOIN databaseName2.dbo.table3 AS t3
            ON t2.OtherColumn = t3.OtherColumn
WHERE  ....
Reply to this Message
JoyRose - 06 Sep 2007 15:35 GMT
Ok. Thanks, guys.

I'll give that a shot.
Will I be able to query 2 tables from one db
and do a LEFT JOIN to one table from the other db?
Reply to this Message
JoyRose - 06 Sep 2007 18:35 GMT
I was able to use some sample data to create a simple Left Join and got results
using 2 different databases.
Thank you. I learned something.

I need to learn more though.
If I'm going to blend the final results from my queries into the existing
code, I need to end up with query results, I think. The existing code uses the
results of a query to build an array with structures.

Here's kinda pseudo-code for what I need to end up with:

I need the results of my first query to get the data for the intermediate
query and that resulting data to use in the last query from the other database.
Then I need to fold the results of the last query into the data from the first
query. That query result is then used in buidling the array.

You may already have mentioned an option to do that,. If you wouldn't mind
repeating it and providing a bit more detail, I'll try again.

Thank you all.
Reply to this Message
cf_dev2 - 06 Sep 2007 19:16 GMT
> uses the results of a query to build an array with structures.

Why not just return the query?

> Then I need to fold the results of the last query into the data from the
first query.

In short it sounds like you want to retrieve information from (3) tables:  
PEdocs, Sources and IHSFacility.  If the IHSFacility table contains a matching
record you want to use the IHSFacility.SourceTitle.  Otherwise, you want to use
the Sources.SourceTitle?  

You could achieve that with a LEFT JOIN and a CASE statement.  Here is
psuedo-sql example

SELECT  Sources.sourceID,
        CASE WHEN IHSFacility.SourceTitle IS NULL
                  THEN Sources.SourceTitle
             ELSE IHSFacility.SourceTitle
    END AS SourceTitle

FROM    database1.dbo.PEdocs
    INNER JOIN database1.dbo.Sources ON PEdocs.fk_sourceID = Sources.sourceID
    LEFT JOIN database2.dbo.IHSFacility ON IHSFacility.fac_ID =
Sources.sourceFacID
WHERE   PEdocs.deleted IS NULL
AND    UPPER(PEdocs.docLetter) =
        <CFQUERYPARAM value="#attributes.letter#" cfsqltype="CF_SQL_CHAR"
maxlength="1">
Reply to this Message
JoyRose - 06 Sep 2007 20:37 GMT
The initial search results displayed are based on a url value. Those records
are
a result of numerous queries beginning with this query:
 <cfquery name="qdocSourceSort" datasource="#request.peds#">   
select  *                       
from PEdocs, Sources                   
WHERE deleted IS NULL               
and UPPER(docLetter) = <CFQUERYPARAM value="#attributes.letter#"
cfsqltype="CF_SQL_CHAR" maxlength="1">
order by DocName
</cfquery>

The array is created. Other queries on different tables are run to return
either single or multiple values, depending on the table. Those  values are
added to the array. The Sources and IHS Facility table results are gathered in
this way.

Using this process worked fine when I was sorting on the DocName. The app. was
put into production. Then the customer decided he wants to sort on other
columns - like SourceTitle.

What I need to do is get the initial query to sort on sourceTitle instead of
DocName.

PEDocs matches Source on sourceID. Sources matches IHSFacility on facID.
I guess I need to loop if I can't use "IN ValueList()" with an outerJoin and
if I can't use the where clause in my initial query and have the results of
that be the basis of records for the other queries in the Join.

There are zillions of Facilities records. I didn't want to query the entire
table or even cache it to get what I need for the different subsets of records
that users are searching for. I'm not sure that's what you were getting at with
your SELECT statement. I'm not clear how you were proposing the SELECT
statement would work.

Ian had suggested this:
..... the next option I
see is an ugly looping solution. Loop over one record set and build a
new query data structure combining the data from the other record set as
relevant. I can see ways to do this by looping over a QofQ or by
creating an associative structure of one record set and reference the
keys to make the connection to the first record set results. I would
probably try the latter solution first.

Is that what I need to do?  I wasn't clear about how to do that.

I appreciate your responses.
I hope I'm clearly responding to your comments.
Thanks for your patience!
Reply to this Message
cf_dev2 - 06 Sep 2007 21:51 GMT
> I guess I need to loop if I can't use "IN ValueList()"
> with an outerJoin and if I can't use the where clause in my initial query
> and have the results of that be the basis  of records for the other queries
> in the Join.

Why do you think you can't use a WHERE clause here?  If you add a LEFT JOIN to
the original query, it should return all of the same records, plus matching
information from the IHSFacility  table (if any).  That information could then
be used for sorting.  

> get the initial query to sort on sourceTitle instead of DocName.

IIRC you said (2) tables contain a column named SourceTitle.  Which
SourceTitle column are you referring to?  Its a bit confusing as your original
query uses SELECT * and doesn't indicate which columns belong to which tables.
Reply to this Message
JoyRose - 07 Sep 2007 15:45 GMT
PEDocs  fk_sourceID matches Sources sourceID
Sources sourceFacID matches IHSFacility facID
* Sources sourceFACID is not unique; many source records have the same FACID.
Sources has the SourceTitle column
Facilities has the source title information in a column called fac_Facility .
Is there a way to get  fac_Facility AS sourceTitle?

Here is the code I tried:

<cfquery name="qalphaResults" datasource="#request.peds#">
select *
from #request.pedb#PEDocs d, #request.pedb#Sources s
where deleted IS NULL
and UPPER(docLetter) = <CFQUERYPARAM value="#attributes.letter#"
cfsqltype="CF_SQL_CHAR" maxlength="1">
LEFT JOIN  #request.pedb#Sources s ON d.fk_sourceID = s.sourceID   
LEFT JOIN #request.generaldb#IHSFacility f ON s.sourceFACID = f.FACID
where s.sourceFacID  IS NOT NULL
</cfquery>

I got an error:
Message: Error Executing Database Query.
Detail: [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near
the keyword 'LEFT'.

I got the same error message trying this:

<cfquery name="qalphaResults" datasource="#request.peds#">
select *
from #request.pedb#PEDocs d, #request.pedb#Sources s
where deleted IS NULL
and UPPER(docLetter) = <CFQUERYPARAM value="#attributes.letter#"
cfsqltype="CF_SQL_CHAR" maxlength="1">
and d.fk_sourceID = s.sourceID
LEFT JOIN #request.generaldb#IHSFacility f ON s.sourceFACID = f.FACID
where s.sourceFacID  IS NOT NULL
</cfquery>

Thanks again for your help!
Reply to this Message
cf_dev2 - 07 Sep 2007 16:51 GMT
> Here is the code I tried:

1. JOIN statements belong in the FROM clause.  You've got them mixed in with
the WHERE clause.  See my earlier psuedo-code for an example. You can find out
more about JOINS here

http://www.w3schools.com/sql/sql_join.asp

2. Don't use select *.  Use a select list to specify the columns you need
Reply to this Message
JoyRose - 07 Sep 2007 17:44 GMT
Doing this:

<cfquery name="qalphaResults" datasource="#request.peds#">
select d.fk_sourceID, s.sourceFACID, s.sourceSourceTitle, f.FACID,
f.fac_Facility AS "sourceTitle"
from #request.pedb#PEDocs d
INNER JOIN  #request.pedb#Sources s ON d.fk_sourceID = s.sourceID
LEFT JOIN #request.generaldb#IHSFacility f ON f.FACID = s.sourceFACID
where deleted IS NULL
and UPPER(docLetter) = <CFQUERYPARAM value="#attributes.letter#"
cfsqltype="CF_SQL_CHAR" maxlength="1">   
and s.sourceFacID IS NOT NULL        
</cfquery>

Message: Error Executing Database Query.
Detail: [Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name
'FACID'.

Based on the tutorial at w#schools.com, I think the problem may be that
neither f.FACID nor s.sourceFACID are primary keys. There will be multiple
times in s.sourceFACID where the same value is used.

If this method won't work, do you know a way I can use my final array with
structures and sort that by sourceTitle? I had tried to find a way to do that
earlier, but I didn't see any resources providing a favorable answer.

Thanks for sticking this out with me!
Reply to this Message
cf_dev2 - 07 Sep 2007 21:10 GMT
> f.FACID,

Wasn't that column called fac_id in the original post?

You're going to have to spend some time on the query, fixing any typos and
syntax errors.   You should also review BOL (books online) to familiarize
yourself with JOINS.  Its often helpful to start with hard-coded values and
test until you've got the right syntax.  Then add the dynamic values back in.
Reply to this Message
cf_dev2 - 07 Sep 2007 21:11 GMT
> Based on the tutorial at w#schools.com, I think the problem may be that
> neither f.FACID nor s.sourceFACID are primary keys. There will be multiple
> times in s.sourceFACID where the same value is used.

You can JOIN on most any column, even if its a primary key or not.  Whether it
returns the correct results is a different story.
Reply to this Message
JoyRose - 10 Sep 2007 15:10 GMT
Thanks for all your help.
I'll look into JOINs more fully.
You were right, the original
column name was f.FAC_ID.
I need to take a break from
this and come back to it.
I'm getting sucked into a
vortex!  :rose;
Reply to this Message
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.