I'm trying to use normalization techniques and figure out how the best way to setup and query some tables and I'm not making much progress. I would appreciate any suggestions (I'm new to data modeling). If the way I've set the tables up is okay then I just need help with the SQL to query them. If there is a better way to setup the tables then let me know. I would prefer to not change the SEGMENT and PROGRAM tables as they are already being used.

SENERIO

I have two tables that have a many to many relationship, SEGMENT and PROGRAM.

One SEGMENT may contain many PROGRAMS
One PROGRAM may belong to many SEGMENTS

table segment
Column
------------------------------
SEGMENT_ID (PK)
SEGMENT_DESC

table program
Column
------------------------------
PROGRAM_ID (PK)
PROGRAM_DESC


I need to provide a way to indicate which programs belong to which segments. In my attempt to do this I created a table SEGPROG_RELATE.

table segprog_relate
Column
------------------------------
SEGPROG_ID
SEGMENT_ID (FK to SEGMENT.SEGMENT_ID)
PROGRAM_ID (FK to PROGRAM.PROGRAM_ID)

I put test data into each table so I could attempt to figure out the SQL needed to display records base on certain criteria (i.e. show all programs in segment "01" - or - show all segments that program "004" is in).

Here is the sample data:

SEGMENT_ID SEGMENT_DESC
---------- ---------------
01 Segment Desc 01
02 Segment Desc 02
03 Segment Desc 03
04 Segment Desc 04
05 Segment Desc 05

PROGRAM_ID PROGRAM_DESC
---------- ---------------
001 Program Desc 01
002 Program Desc 02
003 Program Desc 03
004 Program Desc 04
005 Program Desc 05

SEGPROG_ID SEGMENT_ID PROGRAM_ID
---------- ---------- ----------
1 01 003
2 01 004
3 02 001
4 02 003
5 03 005
6 03 002
7 04 001
8 04 005
9 05 005
10 05 003
11 05 002

What is the syntax of the SQL statement needed to display SEGMENT.SEGMENT_DESC and PROGRAM.PROGRAM_DESC for all programs in segment "04"? this should return:

SEGMENT.SEGMENT_DESC PROGRAM.PROGRAM_DESC
-------------------------------------------
Segment Desc 04 Program Desc 01
Segment Desc 04 Program Desc 05


Thanks,

Lisa