List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:February 3 2001 11:47pm
Subject:Re: Intersection of two lists
View as plain text  
In the last episode (Feb 01), Jeff Sorenson said:
> Does anyone know of an elegant way to determine, within a query, whether 
> two arbitrary lists have common element?
> 
> I am quite fond of queries using "in", e.g:  select * from A where 23 in 
> (A.list)   - where A.list is a comma-separated list of numbers in a 
> varchar(255) field.
> 
> Now I need something like:  select * from A where (23,43,21,52,74) in 
> (A.list) to work - but it doesn't.
...
> I suppose what I'm looking for is the equivolent of an "intersection" 
> operation between two sets.  But I can't use MySql sets because they are 
> bit fields that allow only 64 members.  Does anyone know a more efficient 
> or elegant way to do this with MySQL?

You should be able to create a UDF set_intersect() function that takes
two comma-delimited strings and returns a list of the common
elements.  Then you could do 
  WHERE set_intersect("23,43,21,52,74", A.list) != ""


-- 
	Dan Nelson
	dnelson@stripped
Thread
Intersection of two listsJeff Sorenson3 Feb
  • Re: Intersection of two listsDan Nelson4 Feb