On 19 Aug 2002, at 9:36, Aleksandar Bradaric wrote:
> It's that OR that makes the mess. Try with:
>
> -> WHERE CompanyDetails.CompanySuspended='0' AND
> -> CompanyDNSZones.ZoneName='megalan.co.za' AND
> -> (CompanyDNSZones.ZoneServices LIKE '%HasMail%' OR
> -> CompanyDNSZones.ZoneServices LIKE '%HasMailingList%');
Since any string that contains 'HasMailingList' also contains
'HasMail', there's no point in adding the last bit. This should return
the same results more efficiently:
WHERE CompanyDetails.CompanySuspended='0' AND
CompanyDNSZones.ZoneName='megalan.co.za' AND
CompanyDNSZones.ZoneServices LIKE '%HasMail%';
If it's necessary to distinguish between 'HasMail' and 'HasMailingList'
(as it presumably would be in other queries), then you can't use LIKE.
Use FIND_IN_SET() instead (or rename the set elements):
WHERE CompanyDetails.CompanySuspended='0' AND
CompanyDNSZones.ZoneName='megalan.co.za' AND
( FIND_IN_SET('HasMail', CompanyDNSZones.ZoneServices) OR
FIND_IN_SET('HasMailList', CompanyDNSZones.ZoneServices) );
Another possibility is to do bit operations on the set. In this case,
HasMail is 1 and HasMailingList is 2, so the criteria should be
WHERE CompanyDetails.CompanySuspended='0' AND
CompanyDNSZones.ZoneName='megalan.co.za' AND
CompanyDNSZones.ZoneServices & 3;
[Filter fodder: SQL]
--
Keith C. Ivey <[EMAIL PROTECTED]>
Tobacco Documents Online
http://tobaccodocuments.org
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
