using IN() on Column with comma separated value

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

using IN() on Column with comma separated value

Post 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?
User avatar
Inkyskin
Forum Contributor
Posts: 282
Joined: Mon Nov 19, 2007 10:15 am
Location: UK

Post by Inkyskin »

The lid column isnt a unique number is it?
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Post by kendall »

Inkyskin wrote:The lid column isnt a unique number is it?
its the primary key :?: :?
dancaragea
Forum Newbie
Posts: 10
Joined: Sat Oct 14, 2006 2:03 pm

Post 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.
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Post 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"
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Post by kendall »

do you see an alternative to use besides IN() a subquery maybe? just wondering
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post by VladSun »

There are 10 types of people in this world, those who understand binary and those who don't
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Post by kendall »

Post Reply