How create results with an Outer Join using 2 different databases

[JoyRose]

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.

[Newsgroup_User]

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.

[JoyRose]

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.

[Dan_Bracuk]

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.

[cf_dev2]

> 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?

[Newsgroup_User]

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>

[JoyRose]

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.

[Dan_Bracuk]

quote:

---

Originally posted by: JoyRose
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?

---

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

[cf_dev2]

Edit - Some of this was already mentioned in other posts

Originally posted by: JoyRose
> 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 ....

[Newsgroup_User]

"

quote:

---

Originally posted by: JoyRose
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?

---

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.

[JoyRose]

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?

[JoyRose]

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.

[cf_dev2]

JoyRose wrote:
> uses the results of a query to build an array with structures.

Why not just return the query?

JoyRose wrote:
> 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. I'm guessing about your column names, so they are likely wrong.

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

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">
ORDER BY MergedSourceTitle

Edit - changed psuedo sql code

[JoyRose]

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!

[cf_dev2]

JoyRose wrote
> 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.

[JoyRose]

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!

[cf_dev2]

> 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

[JoyRose]

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!

[cf_dev2]

> 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.

[cf_dev2]

> 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.

[JoyRose]

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!