I have 3 files. 1 shapefile and 2 DBF files. For those of who are not a familiar with shapefiles(GIS data format), they are essentially a database table with columns and rows that have spatial geometry such as point,line,polygon etc.. which can be displayed visually and used in calculations in a GIS software

shapefile - trucknetwork: 564 records(column sri links to table1) - specific truck road network in NJ with no road names

gid integer NOT NULL DEFAULT nextval('trucknetwork_gid_seq'::regclass),
id numeric(10,0),
sri character varying(20),
mp_start double precision,
mp_end double precision,
descriptio character varying(50),
road_type character varying(50),
geom geometry(MultiLineStringM),
CONSTRAINT trucknetwork_pkey PRIMARY KEY (gid)

Table1 - 488715 records(links to shapefile with sri column and table2 with seg_guid column) - start and end points of all NJ roads, with seg_guid that links to table2

gid integer NOT NULL DEFAULT nextval('table1_gid_seq'::regclass),
  objectid integer,
  seg_guid character varying(38),
  sri character varying(20),
  route_type numeric,
  sld_mp_st numeric,
  sld_mp_end numeric,
  CONSTRAINT table1_pkey PRIMARY KEY (gid)

Table2 - 669557 records(Links to table1 with seg_guid column) - all NJ road names

gid integer NOT NULL DEFAULT nextval('table2_gid_seq'::regclass),
objectid integer,
seg_guid character varying(38),
s_name character varying(254),
CONSTRAINT table2_pkey PRIMARY KEY (gid)

I want to join these 3 tables into 1 table - with the result being 564 records of each road segment with its correct name. It is a 1 to Many relationship.

I have posted this question on GIS stackexchange but I did not get a lot of feedback and I want to do this task in SQL not a GIS software

I have tried left join, inner join - and I have tried the group by but for every query I keep getting over 100,000 + records

select shp.descriptio as descr, shp.road_type as road_type, shp.geom as geom, t2.s_name as street_name 
      into networkEX 
from trucknetwork as shp 
   join table1 as t1 on shp.sri = t1.sri 
   join table2 as t2 on t1.seg_guid = t2.seg_guid; 

I am not sure what the logic should be, subquery? do i have to break this up into smaller steps?

I have 3 files. 1 shapefile and 2 DBF files. For those of who are not a familiar with shapefiles  (GIS data format), they are essentially database tables with rows and columns that have spatial geometry such as point, line, polygon etc., which can be displayed visually and used in calculations in a GIS software.

trucknetwork (shapefile) - 564 rows (column sri links to table1) - specific truck road network in NJ with no road names:

gid integer NOT NULL DEFAULT nextval('trucknetwork_gid_seq'::regclass),
id numeric(10,0),
sri character varying(20),
mp_start double precision,
mp_end double precision,
descriptio character varying(50),
road_type character varying(50),
geom geometry(MultiLineStringM),
CONSTRAINT trucknetwork_pkey PRIMARY KEY (gid)

table1 - 488715 rows (links to shapefile with sri column and table2 with seg_guid column) - start and end points of all NJ roads:

gid integer NOT NULL DEFAULT nextval('table1_gid_seq'::regclass),
objectid integer,
seg_guid character varying(38),
sri character varying(20),
route_type numeric,
sld_mp_st numeric,
sld_mp_end numeric,
CONSTRAINT table1_pkey PRIMARY KEY (gid)

table2 - 669557 rows (links to table1 with seg_guid column) - all NJ road names:

gid integer NOT NULL DEFAULT nextval('table2_gid_seq'::regclass),
objectid integer,
seg_guid character varying(38),
s_name character varying(254),
CONSTRAINT table2_pkey PRIMARY KEY (gid)

I want to join these 3 tables into 1 table - with the result being 564 rows of each road segment with its correct name. It is a one-to-many relationship.

I have posted this question on GIS stackexchange but I did not get a lot of feedback and I want to do this task in SQL not a GIS software.

I have tried left join, inner join - and I have tried the group by but for every query I keep getting over 100,000 + records

select shp.descriptio as descr, shp.road_type as road_type, shp.geom as geom, t2.s_name as street_name 
      into networkEX 
from trucknetwork as shp 
   join table1 as t1 on shp.sri = t1.sri 
   join table2 as t2 on t1.seg_guid = t2.seg_guid; 

I am not sure what the logic should be. Subquery? Do I have to break this up into smaller steps?