Search based on two combined columns using IN

  • 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.

  • 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



    Clear Sky SQL
    My Blog[/url]

  • 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

  • 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