CODEWITHSUNDEEP
sqljoinconditional-statements
Vasista B
Vasista B

Reputation: 339

Join statement with condition in sql

a.geosid    a.Latitude   a.Longitude    b.Latitude  b.Longitude     b.geosid
9589565     -36.013472  -71.426018      -36.0135    -71.426         9589565
9586071     -36.015     -71.498         -36.1104    -71.4416        9586071
9589565     -36.013473  -71.426017      -36.0135    -71.426         9589565

The data above is formed by running a query in sql something like 

SELECT *
FROM   [ChileCSVimports].[dbo].[tLocation] a
       JOIN AIRGeography..tGeography b
         ON a.GeographySID = b.GeographySID

I need to select data such that the two lat-long of two different tables if they differ by 0.0002 (respectively) or more then don't select the data --

Upvotes: 0

Views: 436

Answers (3)

KeithS
KeithS

Reputation: 71563

Sounds fairly straightforward, since they're both numbers. Since you're using an inner join, you can either put the criteria in the join:

SELECT *
FROM   [ChileCSVimports].[dbo].[tLocation] a
       JOIN AIRGeography..tGeography b
         ON a.GeographySID = b.GeographySID
         AND ABS(a.Latitude - b.Latitude) < 0.0002
         AND ABS(a.Longitude - b.Longitude) < 0.0002

... or in a separate WHERE clause:

SELECT *
FROM   [ChileCSVimports].[dbo].[tLocation] a
       JOIN AIRGeography..tGeography b
         ON a.GeographySID = b.GeographySID
WHERE    ABS(a.Latitude - b.Latitude) < 0.0002
         AND ABS(a.Longitude - b.Longitude) < 0.0002

Either way, simply let the DB server do the math while retrieving your result set. This ABS() function is available on most DBMSes with identical syntax; PostgreSQL uses the "@" operator as a shorthand (@ -5.4 evaluates to 5.4).

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93704

Add the filters in where clause. Try this.

SELECT *
FROM   [ChileCSVimports].[dbo].[tLocation] a
       JOIN AIRGeography..tGeography b
         ON a.GeographySID = b.GeographySID 
Where ABS(a.Latitude - b.Latitude) < 0.0002  
and   ABS(a.Longitude - b.Longitude) <0.0002

Upvotes: 1

Hart CO
Hart CO

Reputation: 34774

You can add to your JOIN criteria or use WHERE criteria to compare the values:

SELECT *
FROM   [ChileCSVimports].[dbo].[tLocation] a
       JOIN AIRGeography..tGeography b
         ON a.GeographySID = b.GeographySID 
        AND ABS(a.Latitude - b.Latitude) < 0.0002 
        AND ABS(a.Longitude - b.Longitude) < 0.0002

Use ABS() to return the absolute value so you don't have to worry about which value is bigger than the other.

Upvotes: 1

Related Questions