List:General Discussion« Previous MessageNext Message »
From:Keith C. Ivey Date:August 19 2002 2:16pm
Subject:Re: where clause structures
View as plain text  
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 <keith@stripped>
Tobacco Documents Online
http://tobaccodocuments.org
Thread
where clause structuresChris Knipe18 Aug
  • Re: where clause structuresChris Knipe18 Aug
  • where clause structuresTerence19 Aug
  • Re: where clause structuresAleksandar Bradaric19 Aug
    • Re: where clause structuresKeith C. Ivey19 Aug