October 20, 2009 at 7:13 am
I have tried using the following but I don't think this is working as expected:
SELECT origin_latitude, origin_longitude, COUNT(*) AS Structures
FROM towergrid_reference_natowers
WHERE (distance_miles <= 3) AND remote_latitude not in (SELECT remote_latitude FROM towergrid_reference_clli) AND remote_longitude not in (SELECT remote_longitude FROM towergrid_reference_clli)
GROUP BY origin_latitude, origin_longitude
As you can probably tell I am trying to make sure the items in the CLLI table are not in my data set.
October 20, 2009 at 7:19 am
Left join would be better...
SELECT origin_latitude, origin_longitude, COUNT(*) AS Structures
FROM towergrid_reference_natowers
left join towergrid_reference_clli
on towergrid_reference_clli.remote_latitude = towergrid_reference_natowers.remote_latitude
and towergrid_reference_clli.remote_longitude = towergrid_reference_natowers.remote_longitude
WHERE (distance_miles <= 3) AND
and towergrid_reference_clli.remote_latitude is null
GROUP BY origin_latitude, origin_longitude
October 20, 2009 at 7:22 am
Change from using an In statement to a Left Join.
SELECT
origin_latitude,
origin_longitude,
COUNT(*) AS Structures
FROM
towergrid_reference_natowers AS towers
LEFT OUTER JOIN towergrid_reference_clli AS clli
ON towers.remote_latitude = clli.remote_latitude
AND towers.remote_longitude = clli.remote_longitude
WHERE
(distance_miles <= 3)
AND clli.[primary key column] IS NULL
GROUP BY
origin_latitude,
origin_longitude;
Without the actual table definitions, I can't make this fully functional, but that should point you in the right direction.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 20, 2009 at 7:34 am
Thanks alot for pointing me in the right direction.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply