20 Replies Latest reply on Sep 10, 2007 7:10 AM by JoyRose

    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.
        • 1. Re: How create results with an Outer Join using 2 different   databases
          Level 7
          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.

          • 2. Re: How create results with an Outer Join using 2 different databases
            JoyRose Level 1
            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.
            • 3. Re: How create results with an Outer Join using 2 different databases
              Dan Bracuk Level 5
              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.
              • 4. Re: How create results with an Outer Join using 2 different databases
                cf_dev2 Level 1
                > 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?

                • 5. Re: How create results with an Outer Join using 2   different databases
                  Level 7
                  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>

                  • 6. Re: How create results with an Outer Join using 2 different databases
                    JoyRose Level 1
                    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.
                    • 7. Re: How create results with an Outer Join using 2 different databases
                      Dan Bracuk Level 5
                      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.
                      • 8. Re: How create results with an Outer Join using 2   different databases
                        Level 7
                        "
                        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.

                        • 9. Re: How create results with an Outer Join using 2 different databases
                          JoyRose Level 1
                          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?
                          • 10. How create results with an Outer Join using 2 different databases
                            cf_dev2 Level 1
                            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 ....


                            • 11. Re: How create results with an Outer Join using 2 different databases
                              JoyRose Level 1
                              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.
                              • 12. How create results with an Outer Join using 2 different databases
                                cf_dev2 Level 1
                                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
                                • 13. Re: How create results with an Outer Join using 2 different databases
                                  JoyRose Level 1
                                  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!

                                  • 14. Re: How create results with an Outer Join using 2 different databases
                                    cf_dev2 Level 1
                                    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.

                                    • 15. Re: How create results with an Outer Join using 2 different databases
                                      JoyRose Level 1
                                      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!
                                      • 16. Re: How create results with an Outer Join using 2 different databases
                                        cf_dev2 Level 1
                                        > 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
                                        • 17. Re: How create results with an Outer Join using 2 different databases
                                          JoyRose Level 1
                                          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!
                                          • 18. Re: How create results with an Outer Join using 2 different databases
                                            cf_dev2 Level 1
                                            > 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.



                                            • 19. Re: How create results with an Outer Join using 2 different databases
                                              cf_dev2 Level 1
                                              > 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.
                                              • 20. Re: How create results with an Outer Join using 2 different databases
                                                JoyRose Level 1
                                                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!