Skip to main content
A
Anonymous
January 22, 2011
Question

Query Help Please

  • January 22, 2011
  • 4 replies
  • 1283 views

Hi... having problems with a query.  Any assistance would be

much appreciated.

Two queries with identical columns: Villages_Query_1 and Villages_Query_2.

Both have these columns: Village_ID, Village_Name, Player_ID.

I need to find all records in Villages_Query_2 where the Village_ID's match but the Player_ID's have changed.

Example Village_Query_1

Village_IDVillage_NamePlayer_ID
1Houston1
2Dallas2
3Chicago3

Example Village_Query_2

Village_IDVillage_NamePlayer_ID
1Houston1
2Phoenix4
3Chicago3
4New York5

In this case, Village_ID = 2, has changed names (Dallas to Phoenix) and the Player_ID has changed (2 to 4).  In addition, a new record was added.

The eventual output I need is to be able to report the following:

Player 2 village "Dallas" was taken by Player 4 and renamed "Phoenix".

New York is a new village owned by Player 5.

How the heck do I do this??  I have been trying query after query... reading about query of queries and JOINS and and and... I am now completely confused.

Help appreciated.

Mark

    This topic has been closed for replies.

    4 replies

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    January 23, 2011

    And a suggestion that uses query of a query:

    <cfquery name="q1" dbtype="query">
    select village_query_1.player_id as player1, village_query_1.village_name as village1, village_query_2.player_id as player2, village_query_2.village_name as village2
    from village_query_1, village_query_2
    where village_query_1.village_id = village_query_2.village_id
    and village_query_1.village_name <> village_query_2.village_name
    </cfquery>

    <cfquery name="q2" dbtype="query">
    select village_name as new_village, player_id
    from village_query_2
    where village_id NOT IN (#valueList(village_query_1.village_id)#)
    </cfquery>

    <cfdump var="#q1#"><br>
    <cfdump var="#q2#">

    A
    Anonymous
    January 23, 2011

    First, just wanted to say "Thanks" for all the attempts at help.

    Still struggling....

    The last one gave me this...

    500

    ROOT CAUSE: 
com.seefusion.SeeFusionKillError: SeeFusion terminated request: Page Time at 50375ms >= limit of 50000ms (rule "latentpages")
     at com.seefusion.ud.e(ud.java:338)

    The others are giving me timeout errors.

    I tried just looping through the records to write them to a database on the server... but even that times out.

    Frustration grows....

    I called my hosting provider and asked them to look at the timer and see if it could be increased... the tech is looking into it.

    Let me give some more data, maybe there is a different solution.

    I have two CSV text files that I upload and save on the server.  Identical column definitions.  Roughly 40,000 rows.   I am told that in the future as much as 100,000 rows are possible.   I need to find the changes in the two files....  Player_ID changing, Village_Name changes, New Rows etc.

    I can successfully read and parse the files into individual queries or arrays... but every attempt to correlate the two has failed.

    Open to thoughts... getting tired of banging my head against the desk on this one....

    Thanks

    Mark

    A
    Adam Cameron.
    Inspiring
    January 23, 2011

    I have two CSV text files that I upload and save on the server.  Identical column definitions.  Roughly 40,000 rows.   I am told that in the future as much as 100,000 rows are possible.   I need to find the changes in the two files....  Player_ID changing, Village_Name changes, New Rows etc.

    Right.  You definitely don't want to be doing data manipulation on that scale with CF: it is not the right tool for the job.  Pass the CSV data straight into the DB and process it all on the DB.

    --

    Adam

    A
    Adam Cameron.
    Inspiring
    January 22, 2011

    Rather than all these CF-centric solutions, can you not push all this DB processing back into the DB (where it probably belongs)?

    --

    Adam

    D
    Dan_Bracuk
    Inspiring
    January 22, 2011

    Do a union query of queries.  In the top half, join on village id and have a where clause of the players or the village names being different.  In the bottom half, look for village id's not in query 1.  The valuelist() function will help you with that.

    A
    Anonymous
    January 22, 2011

    Can you explain what you mean by this???

    "Do a union query of queries.  In the top half, join on village id and have a where clause of the players or the village names being different.  In the bottom half, look for village id's not in query 1.  The valuelist() function will help you with that."

    And does UNION work if you are working off queries instead of tables???

    Thanks

    Mark

    D
    Dan_Bracuk
    Inspiring
    January 22, 2011

    Query of queries support union queries.

    If each of your queries is returning 40000 records, query of queries may also time out.  Bringing back that many records may also be an indication that you are doing something inefficiently.  You might be able to achieve your goal by looking for new or changed records in a single query from the database.  What was the sql for each query.

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    January 22, 2011

    <cfset isVillageRenamed = false>
    <cfset isNewVillage = false>
    <cfset village_1_id_list = valuelist(village_query_1.village_id)>

    <!--- the search continues unless a village is renamed or a new village is found --->

    <cfloop query="village_query_1">
        <cfloop query="village_query_2">   
            <cfoutput>
                <cfif village_query_1.village_ID[village_query_1.currentRow] EQ village_query_2.village_ID[village_query_2.currentRow] AND village_query_1.player_ID[village_query_1.currentRow] NEQ village_query_2.player_ID[village_query_2.currentRow] AND NOT isVillageRenamed>
                    Player #village_query_1.player_ID[village_query_1.currentRow]# village "#village_query_1.village_name[village_query_1.currentRow]#" was taken by Player #village_query_2.player_ID[village_query_2.currentRow]# and renamed "#village_query_2.village_name[village_query_2.currentRow]#".<br>
                    <cfset isVillageRenamed = true>
                </cfif>   
                <cfif NOT listFind(village_1_id_list, village_query_2.village_ID[village_query_2.currentRow]) AND NOT isNewVillage>
                    #village_query_2.village_name[village_query_2.currentRow]# is a new village owned by Player #village_query_2.player_ID[village_query_2.currentRow]#.<br>
                    <cfset isNewVillage = true>
                </cfif>       
            </cfoutput>
        </cfloop>
    </cfloop>

    A
    Anonymous
    January 22, 2011

    I the code provided above... I am getting a timeout error.  Each query has 40,000 records... the nested loop is too big...

    The request has exceeded the allowable time limit Tag: cfoutput

    Looking at the valuelist function now to learn about it.

    Mark

    A
    Anonymous
    January 22, 2011

    I tried this.... putting them both into the same query... and it is timing out

    as well.

    <cfset Villages_Comparison_Query = QueryNew("Village_ID, Village_Name_1, Village_Name_2, X_Coordinate, Y_Coordinate, Player_ID_1, Player_ID_2, Village_Points_1, Village_Points_2", "Integer, VarChar, VarChar, Integer, Integer, Integer, Integer, Integer, Integer")>
    <cfloop query="Villages_Query_1">

    <cfset Villages_Query_1_Village_ID = #Villages_Query_1.Village_ID#>

    <cfquery
      name="Village_Match"
         dbtype="query">
         SELECT * FROM Villages_Query_2
         WHERE Village_ID = #Villages_Query_1_Village_ID#
    </cfquery>


    <cfset RowCount = QueryAddRow(Villages_Comparison_Query, 1)>
    <cfset temp = QuerySetCell(Villages_Comparison_Query, "Village_ID", #Villages_Query_1.Village_ID#, #RowCount#)>

    <cfset temp = QuerySetCell(Villages_Comparison_Query, "Village_Name_1", #Villages_Query_1.Village_Name#, #RowCount#)>
    <cfset temp = QuerySetCell(Villages_Comparison_Query, "Village_Name_2", #Village_Match.Village_Name#, #RowCount#)>
    <cfset temp = QuerySetCell(Villages_Comparison_Query, "X_Coordinate", #Villages_Query_1.X_Coordinate#, #RowCount#)>
    <cfset temp = QuerySetCell(Villages_Comparison_Query, "Y_Coordinate", #Villages_Query_1.Y_Coordinate#, #RowCount#)>
    <cfset temp = QuerySetCell(Villages_Comparison_Query, "Player_ID_1", #Villages_Query_1.Player_ID#, #RowCount#)>
    <cfset temp = QuerySetCell(Villages_Comparison_Query, "Player_ID_2", #Village_Match.Player_ID#, #RowCount#)>
    <cfset temp = QuerySetCell(Villages_Comparison_Query, "Village_Points_1", #Villages_Query_1.Village_Points#, #RowCount#)>
    <cfset temp = QuerySetCell(Villages_Comparison_Query, "Village_Points_2", #Village_Match.Village_Points#, #RowCount#)>
    </cfloop>

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