Multiple WHERE values in cfquery

[Guest]

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.

[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.

[Dan_Bracuk]

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?

[Guest]

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!

[Dan_Bracuk · Accepted solution]

Are you familiar with this syntax?

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

[Guest]

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 :(

[Dan_Bracuk]

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.

[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>

[Newsgroup_User]

> 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