[thelist] SQL: Problem with a join, i think

[Matt Warden]

Casey,

On 2/14/06, Casey Crookston <caseyc at internetopus.com> wrote:
> SELECT     *
> FROM         r2Sales_Customer INNER JOIN
>                       c2Business_Type ON r2Sales_Customer.Customer_type =
> c2Business_Type.incremental_id AND
>                       r2Sales_Customer.Name LIKE 'aaa' OR
> r2Sales_Customer.Address1 LIKE 'bbb' AND r2Sales_Customer.Zip LIKE '111'
>
> c2Business _Type contains only eight rows, Prospect, internal referal,
> active customer, etc.  This query, when executed from the file, spits 8
> records -- one for each of the rows in  c2Business _Type.  Why would it
> return the correct results in EM but different when executed off a server???

Hard to say, but this looks like a mistake:

r2Sales_Customer.Address1 LIKE 'bbb' AND r2Sales_Customer.Zip LIKE '111'

You don't have any wildcard here, so this is essentially the same as:

r2Sales_Customer.Address1='bbb' AND r2Sales_Customer.Zip='111'

You probably want something like this:

r2Sales_Customer.Address1 LIKE 'bbb%' AND r2Sales_Customer.Zip LIKE '111%'


--
Matt Warden
Miami University
Oxford, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.