All:

Thanks in advance for any insight. I got turned on to associative
("bridge?") tables by an MCAD friend, but he hasn't had the time lately to
show me how to really utilize them.

This is my first post, and I'm relatively new to MSSQL, so I hope I'm
providing adequate information. I can only hope that I'll be able to help
others in the future in this newsgroup.

Here's the sticky wicket....

I have four tables, one a "main" items table, and three associative tables
(if that's what they're called). The three associative tables store ID
values associated with catalog tables. I have the data INSERT working
properly, no problems there. However, my problem is this:

Using the following statement in a view, I end up with garbage records:
SELECT DISTINCT
                      dbo.tblPathfinderItems.itemID,
dbo.tblPathfinderItems.name, dbo.tblPathfinderItemToGradeLevel.gradeLevelID,
                      dbo.tblPathfinderGradeLevelToContentArea.contentAreaID,
dbo.tblPathfinderContentAreaToTopic.topicID,
                      dbo.tblPathfinderTopicToSubTopic.subTopicID
FROM         dbo.tblPathfinderItems INNER JOIN
                      dbo.tblPathfinderItemToGradeLevel ON
dbo.tblPathfinderItems.itemID = dbo.tblPathfinderItemToGradeLevel.itemID
INNER JOIN
                      dbo.tblPathfinderTopicToSubTopic INNER JOIN
                      dbo.tblPathfinderContentAreaToTopic ON
dbo.tblPathfinderTopicToSubTopic.topicID =
dbo.tblPathfinderContentAreaToTopic.topicID INNER JOIN
                      dbo.tblPathfinderGradeLevelToContentArea ON
                      dbo.tblPathfinderContentAreaToTopic.contentAreaID =
dbo.tblPathfinderGradeLevelToContentArea.contentAreaID ON

itemID, name, gradeLevelID, contentAreaID, topicID, subTopicID
32     test     1     2     6     1     1
32     test     1     2     15   1     1
32     test     1     3     6     1     1
32     test     1     3     15   1     1
33     test     2     4     1     7     5
33     test     2     4     1     7     7
33     test     2     4     5     7     5
33     test     2     4     5     7     7
33     test     2     5     1     7     5
33     test     2     5     1     7     7
33     test     2     5     5     7     5
33     test     2     5     5     7     7



I need my view to show combined itemID, name from the main table, and
gradeLevelID, contentAreaID, topicID, and subTopicID from the three
associative tables, respectively. My gut tells me the design view creating
tools aren't the best way to form the statement I need. If anybody has links
to a good tutorial on this issues, thanks in advance for providing such.

I'm including the DDL for all tables.
========================================
Main table of pathfinder items.

CREATE TABLE [tblPathfinderItems] (
[itemID] [int] IDENTITY (1, 1) NOT NULL ,
[active] [bit] NULL CONSTRAINT [DF_tblPathfinderItems_active] DEFAULT (0),
[state] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[name] [varchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[collectionName] [varchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[collectionURL] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[creditLine] [varchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[locURL] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[suggestedUse] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[additionalInfo] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[imageURL] [varchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dateAdded] [datetime] NULL CONSTRAINT [DF_tblPathfinderItems_dateAdded]
DEFAULT (getdate()),
CONSTRAINT [PK_tblPathfinderItems] PRIMARY KEY  CLUSTERED
(
  [itemID]
)  ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

========================================

The tblPathfinderItemToGradeLevel associates the pathfinder item with a
grade level:


CREATE TABLE [tblPathfinderItemToGradeLevel] (
[itemGradeLevelID] [int] IDENTITY (1, 1) NOT NULL ,
[itemID] [int] NULL ,
[gradeLevelID] [int] NULL ,
[dateAdded] [datetime] NULL CONSTRAINT
[DF_tblPathfinderItemToGradeLevel_dateAdded] DEFAULT (getdate()),
CONSTRAINT [PK_tblPathfinderGradeLevelToItem] PRIMARY KEY  CLUSTERED
(
  [itemGradeLevelID]
)  ON [PRIMARY]
) ON [PRIMARY]
GO

========================================

The tblPathfinderGradeLevelToContentArea associates the grade level to the
content area:

CREATE TABLE [tblPathfinderGradeLevelToContentArea] (
[gradeToContentID] [int] IDENTITY (1, 1) NOT NULL ,
[gradeLevelID] [int] NOT NULL ,
[contentAreaID] [int] NOT NULL ,
[dateAdded] [datetime] NULL CONSTRAINT
[DF_tblPathfinderGradeLevelToContentArea_dateAdded] DEFAULT (getdate()),
CONSTRAINT [PK_tblPathfinderItemsToContents] PRIMARY KEY  CLUSTERED
(
  [gradeToContentID]
)  ON [PRIMARY]
) ON [PRIMARY]
GO

========================================

The tblPathfinderContentAreaToTopic associates the content area to  topic:

CREATE TABLE [tblPathfinderContentAreaToTopic] (
[contentToAreadID] [int] IDENTITY (1, 1) NOT NULL ,
[contentAreaID] [int] NULL ,
[topicID] [int] NULL ,
[dateAdded] [datetime] NULL CONSTRAINT
[DF_tblPathfinderContentAreaToTopic_dateAdded] DEFAULT (getdate()),
CONSTRAINT [PK_tblPathfinderContentToTopic] PRIMARY KEY  CLUSTERED
(
  [contentToAreadID]
)  ON [PRIMARY]
) ON [PRIMARY]
GO
========================================

The tblPathfinderTopicToSubtopic associates the topic to subtopic:

CREATE TABLE [tblPathfinderTopicToSubTopic] (
[topicSubTopicID] [int] IDENTITY (1, 1) NOT NULL ,
[topicID] [int] NOT NULL ,
[subTopicID] [int] NOT NULL ,
[dateAdded] [datetime] NULL CONSTRAINT
[DF_tblPathfinderTopicToSubTopic_dateAdded] DEFAULT (getdate()),
CONSTRAINT [PK_tblPathfinderTopicToSubTopic] PRIMARY KEY  CLUSTERED
(
  [topicSubTopicID]
)  ON [PRIMARY]
) ON [PRIMARY]
GO


With respect (and awe),

Michael

Michael,

You did not provide any test data to insert into your Tables so I have
used the following:

Insert Into tblPathfinderTopicToSubTopic (TopicID, SubTopicId)
Values (2,3)
Insert Into tblPathfinderTopicToSubTopic (TopicID, SubTopicId)
Values (1,4)
Insert Into tblPathfinderTopicToSubTopic (TopicID, SubTopicId)
Values (3,1)
Insert Into tblPathfinderTopicToSubTopic (TopicID, SubTopicId)
Values (6,2)
Insert Into tblPathfinderTopicToSubTopic (TopicID, SubTopicId)
Values (4,6)
Insert Into tblPathfinderTopicToSubTopic (TopicID, SubTopicId)
Values (5,5)



Insert Into tblPathfinderContentAreaToTopic (ContentAreaID, topicID)
Values (1,3)
Insert Into tblPathfinderContentAreaToTopic (ContentAreaID, topicID)
Values (2,1)
Insert Into tblPathfinderContentAreaToTopic (ContentAreaID, topicID)
Values (3,2)
Insert Into tblPathfinderContentAreaToTopic (ContentAreaID, topicID)
Values (4,5)
Insert Into tblPathfinderContentAreaToTopic (ContentAreaID, topicID)
Values (5,6)
Insert Into tblPathfinderContentAreaToTopic (ContentAreaID, topicID)
Values (6,4)

Insert Into tblPathfinderGradeLevelToContentArea ( GradeLevelID,
ContentAreaID)
Values (1,2)
Insert Into tblPathfinderGradeLevelToContentArea ( GradeLevelID,
ContentAreaID)
Values (2,1)
Insert Into tblPathfinderGradeLevelToContentArea ( GradeLevelID,
ContentAreaID)
Values (3, 5)
Insert Into tblPathfinderGradeLevelToContentArea ( GradeLevelID,
ContentAreaID)
Values (4,3)
Insert Into tblPathfinderGradeLevelToContentArea ( GradeLevelID,
ContentAreaID)
Values (5,4)
Insert Into tblPathfinderGradeLevelToContentArea ( GradeLevelID,
ContentAreaID)
Values (6,6)

Insert Into tblPathfinderItemToGradeLevel (ItemID, gradelevelID)
Values (1,1)
Insert Into tblPathfinderItemToGradeLevel (ItemID, gradelevelID)
Values (1,3)
Insert Into tblPathfinderItemToGradeLevel (ItemID, gradelevelID)
Values (2,2)
Insert Into tblPathfinderItemToGradeLevel (ItemID, gradelevelID)
Values (2,5)
Insert Into tblPathfinderItemToGradeLevel (ItemID, gradelevelID)
Values (3,4)
Insert Into tblPathfinderItemToGradeLevel (ItemID, gradelevelID)
Values (3,6)

Insert into tblPathFinderItems (Name)
Values ('Test1')
Insert into tblPathFinderItems (Name)
Values ('Test2')
Insert into tblPathFinderItems (Name)
Values ('Test3')

Based on this content running the following Select seems to return the
results you are after - I think!

Select  a.ItemID,
    a.Name,
    b.GradeLevelID,
    c.ContentAreaID,
    d.TopicID,
    e.SubTopicID

Left Outer  Join tblPathfinderItemToGradeLevel b on a.ItemID = b.ItemID
Left Outer  Join tblPathfinderGradeLevelToContentArea c on
b.GradeLevelID = c.GradeLevelID
Left Outer  Join tblPathfinderContentAreaToTopic d on c.ContentAreaID =
d.ContentAreaID
Left Outer  Join tblPathfinderTopicToSubTopic e on d.TopicID =
e.TopicID


By the way, avoid pre-fixing the name of your Tables with tbl it is not
necessary and not common practice.  Also, try to keep table names short
and concise.

HTH

Barry

Thanks, Barry. I'm going to give this a try now and report any success or
further questions. In the meantime, thanks again for the best practices/tips
and help!

-Michael

Show quoteHide quote

Barry, it worked, sir! I mistyped something and was getting NULLs for my
last two columns (typing it out helps to understand what's happening, I
believe), but I copy/paste your statement and all was perfect.

The only real thanks I can give right now is this:

I'm joyful that there are people such as yourself who will take time to help
others. I'll try to pass on the help when and where I can...

Highest regards,

Michael


Show quoteHide quote

Michael,

It was a pleasure - glad you have your problem fixed.

Barry

Barry,

I think I may have spoken too soon ;)

I've built a search page that pulls from the select statement you provided
(thanks again, learned some things), but I'm getting false records now
instead of redundant records. I've added a 'dateAdded' column to each table
with default value of (getdate()) for uniqueness, but I'm still not forming
my sql statement properly:

Statement:

SELECT     a.activityID,
a.activityName,
a.dateAdded as DateAdded1,
b.subjectID,
b.dateAdded as DateAdded2,
c.gradeID,
c.dateAdded as DateAdded3,
d.lessonCategoryID,
d.dateAdded as DateAdded4

INTO #tempWeatherbug

FROM tblWeatherBugActivities a

LEFT OUTER JOIN
tblWeatherBugActivityToSubject b ON a.activityID = b.activityID

LEFT OUTER JOIN
tblWeatherBugSubjectToGrade c ON b.subjectID = c.subjectID

LEFT OUTER JOIN
tblWeatherBugGradeToLessonCategory d ON c.gradeID = d .gradeID

SELECT
activityID,
activityName,
dateAdded1,
dateAdded2,
dateAdded3,
dateAdded4

FROM #tempWeatherbug


DROP TABLE #tempWeatherbug


==========================

Results:

60 test 1 2005-10-24 15:38:44.947 2005-10-24 15:38:44.993 2005-10-24
15:38:45.040 2005-10-24 15:38:45.180
60 test 1 2005-10-24 15:38:44.947 2005-10-24 15:38:45.103 2005-10-24
15:38:45.150 2005-10-24 15:38:45.180
60 test 1 2005-10-24 15:38:44.947 2005-10-24 15:38:45.103 2005-10-24
15:41:33.887 2005-10-24 15:41:34.013
61 test 2 2005-10-24 15:39:32.337 2005-10-24 15:39:32.383 2005-10-24
15:39:32.417 2005-10-24 15:39:32.527
61 test 2 2005-10-24 15:39:32.337 2005-10-24 15:39:32.383 2005-10-24
15:41:33.467 2005-10-24 15:41:34.013
61 test 2 2005-10-24 15:39:32.337 2005-10-24 15:39:32.447 2005-10-24
15:39:32.493 2005-10-24 15:39:32.527
62 test 3 2005-10-24 15:41:33.090 2005-10-24 15:41:33.170 2005-10-24
15:39:32.417 2005-10-24 15:39:32.527
62 test 3 2005-10-24 15:41:33.090 2005-10-24 15:41:33.170 2005-10-24
15:41:33.467 2005-10-24 15:41:34.013
62 test 3 2005-10-24 15:41:33.090 2005-10-24 15:41:33.637 2005-10-24
15:38:45.150 2005-10-24 15:38:45.180
62 test 3 2005-10-24 15:41:33.090 2005-10-24 15:41:33.637 2005-10-24
15:41:33.887 2005-10-24 15:41:34.013

The 3rd, 5th, 7th, and 9th records don't match date added (and therefore are
false returns).

I've played with your original select statement, but I can't figure out how
to factor out the bad data. Any suggestions or tips will be greatly
appreciated. Thanks in again for the help!!

-Michael

Hi Michael,

I'm not sure I understand what exactly you are looking for.

Why is the Data incorrect?  If you have used the GetDate() function
then the dates will not be the same.  Are you able to clarify a little
further?

Thanks

Barry

Barry, thanks for replying. I'm preparing a response and should have it
posted soon. FYI.

Regards,

Michael

Show quoteHide quote

Barry,

No more than 10 minutes after I send my earlier message, my MCAD buddy dropped me a line and we sorted things out.  Your statement works just fine, my mistake was in my data table structure - I wasn't accounting for many-to-many relationships, thus the false records in my JOIN tables.

In all, thanks again for showing me some tips and helping with the original problem.

Regards,

Michael

Michael,

No problem - you'll owe your friend a few beers the next time you see
him! :-)

Barry

Is the SQL Server Service Running?
Grouping,Paging Logic
sORTING ACROSS MULTIPLE COLUMN
Strange result
Stuck trying to find an efficient whay to do this