EasyCFM.COM ColdFusion Forums / Coding Help! / query problem

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: query problem

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

BlackRose
11-12-2002 @ 8:02 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 1
Joined: Nov 2002


I have a problem: I want to merge 2 tables using coldfusion at runtime as in folowwing sort of tables:
table 1
--------
product | brand | price
webcam1 | 113   | 13€
mouse1  | 113   | 16€
webcam2 | 112   | 10€
mouse2  | 112   | 15€
   |        |      |

table 2
--------
brandnumber | brand
112         | trust
113         | logitec
   |              |

Where the query should result as output:
product | brand    | price
mouse1  | Logitec  |   16€
   |         |          |


I tried it with the folowing code but it failed

<cfsetting enablecfoutputonly="Yes">
<cfparam name="URL.mode" default="categorie">
<cfswitch expression="#URL.mode#">
  <cfcase value="categorie">
<cfquery name="prodlijst" datasource="ftp2575" dbtype="odbc" connectstring="LOGONAUTH=xxxx; UID=xxxx; PW=xxxx">
   SELECT * FROM producten order by productnaam ASC
</cfquery>
   <cfset prdnr="">
   <cfset prdcatnr="">
   <cfset prdcatnm="">
   <cfset prdmrknr="">
   <cfset prdmrknm="">
   <cfset prdnm="">
   <cfset prdbs="">
   <cfset prdlevnr="">
   <cfset prdlevnm="">
   <cfset prdpx="">
   <cfset prdft="">
  <cfloop query="prodlijst">
    <cfset prdnr=ListAppend(prdnr,productnummer,"|")>
    <cfset prdnm=ListAppend(prdnm,productnaam,"|")>
    <cfset prdbs=ListAppend(prdbs,productbeschrijving,"|")>
    <cfset prdpx=ListAppend(prdpx,verkoopprijs,"|")>
    <cfset prdft=ListAppend(prdft,foto,"|")>
     <cfset prdcatnr=#categorie#>
    <cfquery name="cattrans" datasource="ftp2575" dbtype="odbc" connectstring="LOGONAUTH=xxxx; UID=xxxx; PW=xxxx">
     SELECT * FROM Categorieën WHERE categorienummer IN (#prdcatnr#)
     </cfquery>
        <cfset prdcatnm=ListAppend(prdcatnm,Categorienaam,"|")>
    <cfset prdmrknr=#merk#>
     <cfquery name="mrktrans" datasource="ftp2575" dbtype="odbc" connectstring="LOGONAUTH=xxxx; UID=xxxx; PW=xxxx">
     SELECT * FROM merken WHERE merknummernummer IN (#prdmrknr#)
     </cfquery>
        <cfset prdmrknm=ListAppend(prdmrknm,merknaam,"|")>
    <cfset prdlevnr=#leverid#>
     <cfquery name="levtrans" datasource="ftp2575" dbtype="odbc" connectstring="LOGONAUTH=xxxx; UID=xxxx; PW=sxxxx">
     SELECT * FROM Leveranciers WHERE leverid IN (#prdlevnr#)
     </cfquery>
     <cfset prdlevnm=ListAppend(prdlevnm,bedrijf,"|")>
  </cfloop>
  <cfoutput>&prdcatnm=#prdcatnm#&prdmrknm=#prdmrknm#&prdlevnm=#prdlevnm#&totcat=#prodlijst.RecordCount#&status=loadedcat&</cfoutput>
  </cfcase>

</cfswitch>
<cfsetting enablecfoutputonly="no">

<cfsetting enablecfoutputonly="Yes">


BlaCkRose http://www.digart.be

webedge
11-17-2002 @ 1:27 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 238
Joined: Nov 2002

Try this!


<!---Get the mouse!--->
<cfquery datasource="dsn" name="GetProd">
  SELECT *
  FROM Table1
  WHERE Product = 'mouse1'
</cfquery>

<cfoutput>
  <!---Get the manufacturer--->
  <cfset BrandName = #GetProd.Brand#>
  <cfquery="GetMan" datasource="dsn">
     SELECT *
     FROM Table2
     WHERE Brandnumber = '#BrandName#'
  </cfquery>

  <!---Show the result--->
  Product:  #GetProd.product#<br>
  Brand:    #GetMan.Brand#<br>
  Price:    #GetProd.Price#
</cfoutput>



Phil

No matter how much you polish a turd, it's still a turd...

WebmasterEdge.com - Online resources for Webmasters


Website Designed and Developed by Pablo Varando.