8 Replies Latest reply on Dec 15, 2008 6:44 AM by Lithcause

    Multiple WHERE values in cfquery

      Hello,

      I am currently working on a project that involves extracting rows from an SQL table, under several different WHERE values.
      This is my current code:

      quote:

      <cfquery datasource="sourcename" name="queryname">
      select 1,2
      from tablename
      where 2=<cfoutput>'#otherqueryname.3#'</cfoutput>
      </cfquery>


      As you can see, where I have used '#otherqueryname.3#', I am trying to output several 3s. Obviously, this piece of code doesn't work.
      I have also tried using <cfoutput>'#otherqueryname.3#',</cfoutput> but that results in an error due to the remaining comma. I have also tried using <cfoutput>#valuelist(otherqueryname.3)#</cfoutput> but it results in only one 3 actually fetched.

      Please help me with a solution to using several WHERE values.
      Thanks!
      Aiden Camichel

      NOTE: I am also trying to find a reasonable way of applying a file size upload limit to cffile. Any help would be much appreciated.
        • 1. Re: Multiple WHERE values in cfquery
          Drew Nathanson
          I'm not sure if this will help as I don't really have enough to understand what the values are, however I will try:

          <cfquery datasource="sourcename" name="queryname">
          Select 1,2
          from tablename
          where 2 = '#otherqueryname.3#'
          </cfquery>

          This assumes that otherqueryname.3 is a alphanueric and that 2 is also alphanumeric. If not, you need to make sure that are matched definitions. Also, in your example, don't use <cfoutput> as it is already implied.

          You can also combine the 2 queries into 1:

          <cfquery datasource="sourcename" name="queryname">
          Select 1,2
          from tablename
          where 2 = ( Select 3 from sometable where x = <somevalue> )
          </cfquery>

          I hope that helps.

          • 2. Multiple WHERE values in cfquery
            Dan Bracuk Level 5
            If the valuelist only had one item, why would you expect to get a different answer? If the valuelist had more than one item, why are you using = instead of in?
            1 person found this helpful
            • 3. Re: Multiple WHERE values in cfquery
              Ok, I don't think I explained my situation all too well, as I couldn't find a solution from your replies.

              My output is actually not alphanumeric, maybe I shouldn't have used numbers in my example...
              Also, I attempted "in" rather than "=" but had no luck - I'm using Microsoft SQL, does that make a difference?

              Here's the basic explanation of my situation:
              "Table A" has stored information on "groups". The table grows with new groups regularly, but the user exists in only some. The IDs of the groups that the user exists in are queried using the user's ID (from a userID column)
              "Table B" has stored information on "activity" amongst groups, ie new member notifications etc. The table grows with new activity regularly, but only some activity applies to any single group. The ID of the group the activity applies to is stored in a groupID column, making the activity query-able by group.

              So with the user logged in, his user ID available, and his group IDs queried and available. The user now wants to view the activity of all his/her groups at once.

              I'd assume querying the "activity table" using his/her group IDs, ie as a list, would be the correct direction?


              Really hope this helps identify the situation.
              Thanks for your help so far!
              • 4. Re: Multiple WHERE values in cfquery
                Dan Bracuk Level 5
                Are you familiar with this syntax?

                select field1, field2, etc
                from table1 t1 join table2 t2 on t1.somefield = t2.somefield.
                where etc
                • 5. Re: Multiple WHERE values in cfquery
                  quote:

                  Originally posted by: Dan Bracuk
                  Are you familiar with this syntax?

                  select field1, field2, etc
                  from table1 t1 join table2 t2 on t1.somefield = t2.somefield.
                  where etc


                  Not one bit familiar.
                  I gave it a go anyway, but only got errors. But that's because I don't know how to use that syntax :(

                  • 6. Re: Multiple WHERE values in cfquery
                    Level 7
                    > where 2=<cfoutput>'#otherqueryname.3#'</cfoutput>

                    You should probably read up on valueList() (or quoteValueList()) in the
                    docs).

                    http://livedocs.adobe.com/coldfusion/8/functions-pt0_01.html

                    You should also probably run through some SQL tutorials or get a book on
                    basic SQL, to help you get up to speed with it. You'll have to google for
                    those, or ask on an SQL forum how best to approach that.

                    --
                    Adam
                    • 7. Re: Multiple WHERE values in cfquery
                      Dan Bracuk Level 5
                      quote:

                      Originally posted by: aidencamichel

                      Not one bit familiar.
                      I gave it a go anyway, but only got errors. But that's because I don't know how to use that syntax :(



                      The syntax you don't know is very fundamental. If you don't know it, you would be wise to learn it before continuing your project.

                      I've heard good things about the book, Teach Yourself SQL in 10 Minutes by Ben Forta.
                      1 person found this helpful
                      • 8. Re: Multiple WHERE values in cfquery
                        Lithcause
                        if you can post the real structure with table names and value names we can probably help you out better... but here is something i heave that might be similar... first get the info

                        <cfquery name="getInfo" datasource="MyDatabase">
                        SELECT ContactInfo.CID, ContactInfo.Company, ContactInfo.Fname, ContactInfo.Lname, ContactInfo.Address, ContactInfo.City, ContactInfo.State, ContactInfo.Zip,
                        ContactInfo.Email, ContactInfo.Phone, ContactInfo.FarmId, ContactInfo.RecordCreated, FarmInfo.Etc, FarmInfo.Mtc, FarmInfo.Tur, FarmInfo.other,
                        FarmInfo.Type, FarmInfo.qualify, Lab.TotalTests, Lab.TestDate, FarmInfo.FID, Lab.LID, Lab.AITotal, Lab.AIFinding
                        FROM Lab FULL OUTER JOIN
                        FarmInfo ON Lab.FID = FarmInfo.FID FULL OUTER JOIN
                        ContactInfo ON FarmInfo.CId = ContactInfo.CID
                        </cfquery>


                        make sure that you join the tables as above my tables are ContactInfo ,FarmInfo ,and Labmake sure that their primary key is linked as well mine are cid on ContactInfo , fid on FarmInfo, and LID on LAB.
                        then use the info query to retrieve the other stuff where the "getInfo.value = whatever" like the example below...


                        <cfquery name="getTheInFo" datasource="OhioNpip">
                        SELECT sum(Lab.AITotal) as Amount3
                        FROM ContactInfo INNER JOIN
                        FarmInfo ON ContactInfo.CID = FarmInfo.CId INNER JOIN
                        Lab ON FarmInfo.FID = Lab.FID
                        where (AITotal = '#getInfo.AITotal#') and (Address = '#getInfo.elm street#' or '#getInfo.pine street#')
                        </cfquery>
                        1 person found this helpful