Multiple WHERE values in cfquery

Forum|Forum|17 years ago
December 6, 2008
4 replies
1380 views

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.

Advanced techniques

This topic has been closed for replies.

Correct answer 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

N

Newsgroup_User

Inspiring

> 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

D

Dan_BracukCorrect answer

Inspiring

Are you familiar with this syntax?

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

A

Anonymous

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

D

Dan_Bracuk

Inspiring

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.

D

Dan_Bracuk

Inspiring

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?

A

Anonymous

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!

D

Drew Nathanson

Inspiring

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.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded