Where something "OR" something and something or...

[Hollymc]

I thought I would impress my Father in Law by telling him I am a whiz at SQL. I'm alright but no whiz.

These are IP's he wants to see if any of his clients are signing up from. He is diving into the world of CRM and I think he likes me more because I told him I could help. I figured or would work like I have it in context but it is saying ip between is true and start date = that is true, etc...

select c.accountnumber, c.zipcode, c.sourceip
from transactions t, contacts c
where
c.sourceip between '4.2.145.0' and '4.2.145.63' or
c.sourceip between '4.2.170.64' and '4.2.170.127' or
c.sourceip between '4.17.135.32'and '4.17.135.63' and

Startdate = '02-18-2005' and
businessunitid = 3

group by c.accountnumber,c.zipcode,c.sourceip

 

[dbomrrsm]

what is the error ? you arent making much sense - also what data type are the ip and date fields

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[Hollymc]

it runs but the actual query has 3000 lines of:

c.sourceip between '4.2.145.0' and '4.2.145.63' OR
c.sourceip between '4.2.170.64' and '4.2.170.127' OR

and when it finishes it brings up IP's that are not between any of the IP's.

My question then...Is my use of OR correct or no? The query runs but is it giving me correct data - NO

 

[dbomrrsm]

use of or is ok but probably bringing more back than expected because of how ip is held - what data type is ip address ?

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[JamesLean]

DBomrrsm is right about your use of BETWEEN possibly returning false results but I think the main problems are 1) your lack of parentheses and 2) your lack of a JOIN between the tables. You can also use DISTINCT instead of GROUP BY as you are not aggregating anything.

Something like this might be better but obviously would depend on your actual table relationships:

SELECT DISTINCT c.accountnumber, c.zipcode, c.sourceip
FROM transactions t JOIN contacts c ON t.contactid = c.contactid
WHERE (c.sourceip BETWEEN '4.2.145.0' AND '4.2.145.63'
  OR c.sourceip BETWEEN '4.2.170.64' AND '4.2.170.127'
  OR c.sourceip BETWEEN '4.17.135.32' AND '4.17.135.63')
  AND Startdate = '02-18-2005'
  AND businessunitid = 3

--James