Re: matching any values of a set to any values of another set

[Michael Stassen]

tidalx wrote:
Hi, i am trying to match a set of values to another set of values, and to
return true if any of the values of one set matches any values of the second
set.
Something like
SELECT * FROM listings WHERE FIND_IN_SET('Mastercard,Visa', 'AMEX, Cash,
Mastercard, Visa') > 0;
  SELECT * FROM listings
  WHERE 'Mastercard' IN ('AMEX', 'Cash', 'Mastercard', 'Visa')
     OR 'Visa' IN ('AMEX', 'Cash', 'Mastercard', 'Visa');
which I don't think works, because find_in_set can't do more than one value
or because of the comma but i can't seem to figure it out using IN, LIKE or
other operators/functions.
And the values on both sets are not constant, which confuses me even more.
SELECT * FROM listings WHERE FIND_IN_SET('$payment_type1, $payment_type2',
'$payments_accepted_column') > 0;
  SELECT * FROM listings
  WHERE FIND_IN_SET($payment_type1, $payments_accepted_column)
     OR FIND_IN_SET($payment_type2, $payments_accepted_column);
You could probably also do this with one regular expression.  Something like:
  SELECT * FROM listings
  WHERE payments_accepted RLIKE 'Visa|Mastercard';
Any help is appreciated, thank you
There is almost certainly a better way to do this, with a more normalized 
data design.

One option would be to use the SET type for the pay_accepted column
  pay_accepted SET('Cash','Check','AMEX','Discover','Mastercard','Visa')
This would save a lot of space compared to the comma separated list in a 
CHAR() or VARCHAR you apparently have now, and you could still use the 
second SELECT I gave above.  You could also use bit arithmetic.  Each 
element of the set corresponds to a power of 2 (Cash=1, Check=2, AMEX=4, 
Discover=8, and so on).  So, to find listings which accept Cash (=1) or 
Check (=2), you could

  SELECT ... WHERE pay_accepted & (1+2);
or to find listings which accept Cash (=1), Visa (=32), or Discover (=8) you 
could

   SELECT ... WHERE pay_accepted & (1+8+32);
This should be more efficient than ORing several FIND_IN_SETs, I expect, but 
it has the disadvantage that you need to know the number which goes with 
each set element.

Another option would be to make a payments table:
id method
1  Cash
2  Check
3  AMEX
4  Discover
5  MasterCard
6  Visa
and a listing_payment relation table
listing_id  payment_id
1           1
1           2
2           1
2           4
2           5
2           6
...
Then you would do something like
  SELECT DISTINCT ...
  FROM listings l JOIN listing_payment lp ON l.id=lp.listing_id
  JOIN payments p ON p.id=lp.payment_id
  WHERE p.method IN ('Cash', 'Visa', 'Discover');
Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]