SQL to select records from one table that intersect objects from a second table, without duplicates

[Nick Lawrence]

Hello all,

What is the SQL to select records from one table that intersect objects from a second table, without duplicates?

For example;

table1 is mapsheets (regions)

table2 is roads (polylines)

I want to select records from table1 that intersect records from table2, without duplicates.

My problem is that my SQL does return duplicates, that is, if a mapsheet intersects two roads, the mapsheet is returned twice. But I only want the mapsheet to be returned once.

I can accomplish my goal by running the "Delete Duplicate" tool afterwards, but I was hoping for a SQL that accomplishes this in one step.

Ta,

Nick Lawrence

[Peter Horsbøll Møller]

If your road tables has a column with unique IDs you can use a combination of a sub select and a grouped join:

 

Select * from ROADS

Where ID In

   (

   Select ROADS.ID

      From ROADS, MAPSHEETS

      Where ROADS.OBJ Intersects MAPSHEETS.obj

      Group By ROADS.ID

   )

   into Selection

 

I must admit this is the first time I have tried it so I wasn’t sure if MapInfo Pro supported a join in a sub select, but it does.

 

Peter Horsbøll Møller

EMEA Channel Enablement Specialist

Location Intelligence | MapInfo

 

M: +45 29 133 769

peter....@pb.com | @phorsbollmoller

pitneybowes.com/dk | mapinfo.com

--
--
You received this message because you are subscribed to the
Google Groups "MapInfo-L" group.To post a message to this group, send
email to mapi...@googlegroups.com
To unsubscribe from this group, go to:
http://groups.google.com/group/mapinfo-l/subscribe?hl=en
For more options, information and links to MapInfo resources (searching
archives, feature requests, to visit our Wiki, visit the Welcome page at
http://groups.google.com/group/mapinfo-l?hl=en

---
You received this message because you are subscribed to the Google Groups "MapInfo-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mapinfo-l+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

---