Page 1 of 1

using IN() on Column with comma separated value

Posted: Fri Dec 07, 2007 3:53 am
by kendall
HI i am trying to create a query statement where the results returned are based upon the vaule of a column from one table contained IN() a comma separated value of a column in another table thus...

Code: Select all

SELECT MemberId FROM parliament_members, parliament_sitting_logs WHERE parliament_members.pmid IN(parliament_sitting_logs.speakers) AND lid = '23' 
# where speakers has a comma separated value of 66,77,50
i'm not sure if its "suppose" to work as the results would return only 1 row matching result (which is incorrect) as opposed to

Code: Select all

SELECT MemberId FROM parliament_members, parliament_sitting_logs WHERE parliament_members.pmid IN(66,78,50) AND lid = '23'
which would return the correct # of rows results

can the IN() function work like that?

Posted: Fri Dec 07, 2007 4:28 am
by Inkyskin
The lid column isnt a unique number is it?

Posted: Fri Dec 07, 2007 4:31 am
by kendall
Inkyskin wrote:The lid column isnt a unique number is it?
its the primary key :?: :?

Posted: Fri Dec 07, 2007 5:29 am
by dancaragea
You can't use
parliament_members.pmid IN (parliament_sitting_logs.speakers)
the way you want. Mysql will interpret this as
parliament_members.pmid="66,77,50"

You need a query to read the value of parliament_sitting_logs.speakers in an external variable (like in a php variable or at least a mysql variable) then use the variable to build the second query like the one you say its working.

And I am not sure but I think that you also need to tie the 2 tables by a condition in the where clause otherwise you will still get incorrect results.

Posted: Fri Dec 07, 2007 5:48 am
by kendall
Mysql will interpret this as
parliament_members.pmid="66,77,50"
if it does...then how come atleast "1" result is returned? because "66,77,50" != "66"

Posted: Fri Dec 07, 2007 8:19 am
by kendall
do you see an alternative to use besides IN() a subquery maybe? just wondering

Posted: Fri Dec 07, 2007 8:27 am
by VladSun

Posted: Fri Dec 07, 2007 9:27 am
by kendall