Skip to main content
A
Anonymous
May 15, 2011
Answered

database loops on query?

  • May 15, 2011
  • 1 reply
  • 652 views

a field in one table inidcates which rows in it and 2 related tables should be accessed in a query.

all 3 tables share a field: pgID. it is the primary key in the first table and a field in the other 2.

the first query finds which rows relate by a variable: pgfeat

<cfquery name="gtpgsfeat" datasource="mydb">
      SELECT pgID
      FROM pages
      WHERE pgfeat = 1
</cfquery>

i then loop through the query to create a list of all rows that are returned from the query

<cfoutput>
      <cfloop query="gtpgsfeat">
            <cfif ISDefined('catz')>
                  <cfset catz = "#catz#, #gtpgsfeat.pgID#">
            <cfelse>
                  <cfset catz = "#gtpgsfeat.pgID#">
            </cfif>
      </cfloop>
</cfoutput>

i then apply this to a query for the related tables

<cfquery name="gtpgsfeat1" datasource="mydb" dbtype="ODBC">
      SELECT      pages.*, pg_cont.*, pg_text.*
      FROM        pages, pg_cont, pg_text
      WHERE       (pages.pgID IN (#catz#)) AND (pg_cont.pgID IN (#catz#)) AND (pg_text.pgID IN (#catz#)) ;
</cfquery>

the problem. the second query loops within itself - times the recordcount of first query - and i get the same data times the recordcount of the first query.

with a recordcount of 3 for the first query, i get a recordcout of 27 (3 X 3 X 3) for the second query...

example:

abe

     cathy

          john

abe

     cathy

          john

abe

     cathy

          john

? what am i missing here

tnx in advance

This topic has been closed for replies.
Correct answer ilssac

ranger wrote:

? what am i missing here

A basic understanding of relational database programming as best as I can tell from your post.  I hear good things about the books "Teach yourself SQL in 10 minutes" by Ben Forta and "Database Design for Mere Mortals" by Michael Hernandez.  They would show you basic JOIN syntax such as this example where you combine all the related tables into a single record set without multiplying all the rows like the code you where attempting was doing.

<cfquery name="relatedQry" datasource="mydb">
     SELECT
          pages.*,
          pg_cont.*,
          pg_text.*
     FROM
          pages INNER JOIN
          pg_cont ON (pages.pgID = pg_contID) INNER JOIN
          pg_text ON (pages.pgID = pg_test.pgID)
     WHERE
          pgfeat = 1
</cfquery>

P.S. using the * shortcut in your SELECT clause can lead to hard to debug problems if your database table designs are ever changed.

1 reply

ilssac
ilssacCorrect answer
Inspiring
May 15, 2011

ranger wrote:

? what am i missing here

A basic understanding of relational database programming as best as I can tell from your post.  I hear good things about the books "Teach yourself SQL in 10 minutes" by Ben Forta and "Database Design for Mere Mortals" by Michael Hernandez.  They would show you basic JOIN syntax such as this example where you combine all the related tables into a single record set without multiplying all the rows like the code you where attempting was doing.

<cfquery name="relatedQry" datasource="mydb">
     SELECT
          pages.*,
          pg_cont.*,
          pg_text.*
     FROM
          pages INNER JOIN
          pg_cont ON (pages.pgID = pg_contID) INNER JOIN
          pg_text ON (pages.pgID = pg_test.pgID)
     WHERE
          pgfeat = 1
</cfquery>

P.S. using the * shortcut in your SELECT clause can lead to hard to debug problems if your database table designs are ever changed.

A
Anonymous
May 17, 2011

tnx ilssac. joins can make me crazy. sometimes hard to visualize

had to rework your query a bit.

<cfquery name="myquery" datasource="amydb" dbtype="ODBC">
SELECT
          pages.*,
          pg_cont.*,
          pg_text.*
     FROM
          pages
    INNER JOIN
          pg_cont ON (pages.pgID = pg_cont.pgID)
    INNER JOIN
          pg_text ON (pages.pgID = pg_text.pgID)
     WHERE
          pgfeat = 1
</cfquery>

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