Skip to main content
A
Anonymous
February 28, 2011
Answered

Query Of Queries Problem.

  • February 28, 2011
  • 1 reply
  • 1284 views

Hi,

I want to access data from two different datasources without having a common key. For example:

Table1

select stmts

table 2

select stmts

table12 <!---joining the two tables to access data from both the tables--->

select  table1.x, table2.y

where ????? = ?????

How to process these functionality ?

Thanks in advance,

Vijay.

    This topic has been closed for replies.
    Correct answer ilssac

    vijayvijay77 wrote:

    table3<!---join--->

    Slect table1.x, table2.y

    where databasename.dbo.table1.Coomon_field = databasename.dbo.table2.common_field

    Your syntax is a bit muddled there.

    I would expect something like

    SELECT
      aTable.aField,
      aTable.bField,
      bTable.cField
    FROM
      aTable,
      bTable
    WHERE
      aTable.common_field = bTable.common_field

    OR

    SELECT
      aTable.aField,
      aTable.bField,
      bTable.cField
    FROM
      aTable INNER JOIN
      bTable ON aTable.common_field = bTable.common_field

    Your code was missing the FROM clause and you did not list what fields you wanted in the SELECT clause.


    P.P.S

    If you are trying to join two tables from two databases that can not (and can not be made to) see each other.  You can do basically the same thing with Query of Query.  You would just query the desired data from each database.  Then you would use a query of query <CFQUERY>...</CFQUERY> block, substituting the variables with the other table's data in them for the table names in the SQL.

    Just be awary that Query of Query has rather limited joining capability and can only do inner joins.

    1 reply

    ilssac
    ilssac
    Inspiring
    February 28, 2011

    vijayvijay77 wrote:

    Hi,

    I want to access data from two different datasources without having a common key.

    You are probably going to need to explain this more completely?  What do you mean the tables do not have a common key?  Are just talking about the tables not having a defined relationship in the database.  Or are you saying the two tables have no common values?  If the latter, how are the records from each table related?  If the records are not related, how do you desire to join them?

    A
    Anonymous
    February 28, 2011

    Hi,

    Thanks for your reply ilssac.

    The records are related by only one column, which I have to use after the query has been run.

    And I have used the following previously with an error:

    table1

    Select stmnts

    table2

    Select stmnts

    table3<!---join--->

    Slect table1.x, table2.y

    where databasename.dbo.table1.Coomon_field = databasename.dbo.table2.common_field

    The above code didn't work, I dont know why? Hence I was thinking to join the tables and then query the joined table.

    If the above explanation is not clear, please let me know.

    Thanks and I appreciate your response,

    Vijay.

    R
    Reed_Powell-ttnmOb
    Inspiring
    February 28, 2011

    I think we need a better idea of what your queries are doing to help you.  Can you post the CFQUERY statements?  Do they each use the same datasource, or are they doing queries from difference datasources?  Not just difference databases, but different datasources.  If both queries are referencing databases that are accessible from a single datasource, then why not do the JOIN and only have one query?  Also, what database server/version are you using?

    -reed

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices