4 Replies Latest reply on Dec 27, 2006 1:45 PM by Newsgroup_User

    using one query get info from two db's

    WOLFcfm Level 1
      match a field from one table to the same field on other table and then to be able to get other info by using the matching field.

      for example:

      Table1-
      Type, Make

      Table2-
      Type, Model

      using the matching "type" field from each table to get the make and model. i want to do this using one query. not exactly sure how to get it to work...
        • 1. Re: using one query get info from two db's
          insuractive Level 3
          Are the tables in different databases or the same database?

          If they are in the same database, you would use a standard inner join:

          SELECT Table1.Type, Table1.Make, Table2.Model
          FROM Table1 inner join Table2 on Table1.Type = Table2.Type

          If they are in different databases, I know SQL server allows you to set up linked servers so that you can query data from both databases fairly easily. The only difference is you have to user the following syntax for specifying columns: ServerName.Database.User.Table.Column

          e.g. With linked servers:
          SELECT myServerName.myDatabaseName.myDBUser.Table1.Type, myServerName.myDatabaseName.myDBUser.Table1.Make, myOtherServerName.myOtherDatabaseName.myOtherDBUser.Table2.Model
          FROM myServerName.myDatabaseName.myDBUser.Table1 inner join myOtherServerName.myOtherDatabaseName.myOtherDBUser.Table2 on myServerName.myDatabaseName.myDBUser.Table1.Type = myOtherServerName.myOtherDatabaseName.myOtherDBUser.Table2.Type

          Hope that helps!
          • 2. Re: using one query get info from two db's
            Dan Bracuk Level 5
            duplicate post. it's been answered in the Database Access forum
            • 3. Re: using one query get info from two db's
              WOLFcfm Level 1
              thanks Michael for the help, i got it to work but heres the thing when i added the count(*) its now giving me an error, how would i use a count and group by in this scenario? have something like this right now:

              <cfquery name="getmatch" datasource="mysite">
              SELECT top 2 count(*) as counter, table1.type, table1.make, table2.model
              from table1 inner join table2 on table1.type = table2.type
              where table1.number = 1
              group by table1.type
              </cfquery>

              and i keep getting the " Error Executing Database Query." message.
              • 4. Re: using one query get info from two db's
                Level 7
                <cfquery name="getmatch" datasource="mysite">
                SELECT top 2 count(*) as counter, table1.type, table1.make, table2.model
                from table1 inner join table2 on table1.type = table2.type
                where table1.number = 1
                group by table1.type
                </cfquery>

                When you use an aggregate function such as count(), all the columns must
                either be in aggregate functions OR the group by clause. In other words
                all your selected fields need to be added to your group by clause in the
                order you would like the values grouped.

                <cfquery name="getmatch" datasource="mysite">
                SELECT top 2 count(*) as counter, table1.type, table1.make, table2.model
                from table1 inner join table2 on table1.type = table2.type
                where table1.number = 1
                group by table1.type, table1.make, table2.model
                </cfquery>