Discuss "Using a field value in an IN(...) clause" in the MySQL forum.

richyrich · #1 (**permalink**) 04-07-2008, 09:18 AM

Anyone know why this works:-

Code:

select value1,value2,value3 FROM tbl1 WHERE tbl1.value4='a value' AND tb1.value5 IN(2,3,5)

But this doesn't:-

Code:

select value1,value2,value3 FROM tbl1,tbl2 WHERE tbl1.value4='a value' AND tb1.value5 IN(tbl2.value1)

Assuming tbl2.value1 = "2,3,5"

For some reason it only seems to take into account the first value in the table field, but if you type the figures manually, it uses them all.

Any ideas?

mehere · #2 (**permalink**) 04-07-2008, 10:06 AM

i think it has something to do with not going through the whole table with the way the query is. try this:

Code:

SELECT value1,value2,value3 
FROM tbl1 
WHERE tbl1.value4='a value' 
AND tb1.value5 IN
	(SELECT value1 FROM tbl2)

although i think this will work with mySQL 5, it may not work with mySQL 4. not sure. I haven't used mySQL all that much.

richyrich · #3 (**permalink**) 04-07-2008, 10:52 AM

Thanks for the reply mehere.

I tried that aswell, but get the same (wrong) result. I also tried adding the values as a parameter with the same result.

It only seems to accept it, if it's physically in the SQL statement. Something like:-

Code:

strsql = "select value1,value2,value3 FROM tbl1,tbl2 WHERE tbl1.value4='a value' AND tb1.value5 IN(" & my_variable  &")"

This returns the correct results for me. I guess it has something to do with the way it's interpreting the values before it puts them in the query.

It does actually give a result, which is the strange thing, but only based on the first value in the array.

Ah well, guess I'll have to stick with the syntax above.

It's not a major issue because the values I'm inserting aren't selected by the user or anything so it should minimise any problems.

mehere · #4 (**permalink**) 04-07-2008, 10:54 AM

hmmm ... strange. i do know that this works in MSSQL, but my mySQL is a bit rusty ...

richyrich · #5 (**permalink**) 04-07-2008, 11:22 AM

Never mind...Guess I'll just have to stick with it like this.

I just assumed there would be a way to do this, but I guess not...

Thanks mehere...

don94403 · #6 (**permalink**) 04-13-2008, 11:56 AM

Quote:

Originally Posted by richyrich

Anyone know why this works:-

Code:

select value1,value2,value3 FROM tbl1 WHERE tbl1.value4='a value' AND tb1.value5 IN(2,3,5)

But this doesn't:-

Code:

select value1,value2,value3 FROM tbl1,tbl2 WHERE tbl1.value4='a value' AND tb1.value5 IN(tbl2.value1)

Assuming tbl2.value1 = "2,3,5"

For some reason it only seems to take into account the first value in the table field, but if you type the figures manually, it uses them all.

Any ideas?

I think you may have to enclose the value in literal quotes, like:

Code:

... AND tbl1.value5 IN("'"+tbl2.value1+"'")

but I wouldn't bet money on it.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

LinkBacks (?) LinkBack to this Thread: http://www.developerbarn.com/mysql/108-using-field-value-clause.html
Posted By	For	Type	Date
DeveloperBarn Forums - ASP Help, ASP.Net Help, PHP Help, SQL Help, Tutorials, Windows Help	This thread	Refback	04-26-2008 09:36 AM

Discuss "Using a field value in an IN(...) clause" in the MySQL forum.

MySQL - MySQL is a powerful open source database system most commonly used with PHP applications. Discuss MySQL administration and other MySQL related topics here.