Tuesday, 20 August 2013

How do you query for something in SQL and exclude the results from a sub query?

How do you query for something in SQL and exclude the results from a sub
query?

I'm not particularly good at crafting SQL queries. I currently have two
queries that were handed to me. The first searches for contacts in our
system within a 50 mile radius of some address. The second query does the
same thing except it searches a 100 mile radius.
What I need to do is modify the second query so that it excludes the
results from the first query. If you can visualize it we're basically
creating a doughnut shaped area that is 50 miles thick.
Here is the first query (50 mile radius of LA):
SELECT * FROM
(
SELECT ROW_NUMBER() OVER (order by t0.name ASC, t0.accountid) AS
RowNumber, t0.accountid as pkt0
, t0.name as cn
, t0.name as c1
, t1.name as c2
, t1.homephone as c3
, t1.mobilephone as c4
, t1.officephone as c5
, t1.contactid as pkt1
FROM [account] as t0
Left Join [contact] as t1
ON t0.primarycontactid = t1.contactid
WHERE (((
((t0.shippingaddress not like '') AND (t0.shippingaddresslatitude >=
33.6907920399124 ) AND (t0.shippingaddresslatitude <= 34.4136759600876 )
AND (t0.shippingaddresslongitude >= -118.679928573928 ) AND
(t0.shippingaddresslongitude <= -117.807441426072 ))
)) AND (t0.deleted = 0))
) _tmpInlineView
WHERE RowNumber > 0
Here is the second query (100 mile radius of LA):
SELECT * FROM
(
SELECT ROW_NUMBER() OVER (order by t0.name ASC, t0.accountid) AS
RowNumber, t0.accountid as pkt0
, t0.name as cn
, t0.name as c1
, t1.name as c2
, t1.homephone as c3
, t1.mobilephone as c4
, t1.officephone as c5
, t1.contactid as pkt1
FROM [account] as t0
Left Join [contact] as t1
ON t0.primarycontactid = t1.contactid
WHERE (((
((t0.shippingaddress not like '') AND (t0.shippingaddresslatitude >=
33.3293500798248 ) AND (t0.shippingaddresslatitude <= 34.7751179201752 )
AND (t0.shippingaddresslongitude >= -119.11615629035 ) AND
(t0.shippingaddresslongitude <= -117.37121370965 ))
)) AND (t0.deleted = 0))
) _tmpInlineView
WHERE RowNumber > 0
The second query is correct except it must exclude the results of the
first. I'm sure it's probably simple, but I haven't manually written any
SQL in years. Any help is appreciated.

No comments:

Post a Comment