TSQL select clause includes more data than needed

I have an query that is used to pull some data but when I join another table, its duplicating my results for every record it has joined on in the other table.

I'm sure this is a simple issue I am overlooking but cant seem to get it.

My query is here:

SELECT A.[id],
           A.[subject],
           A.[description],
           CONVERT(VARCHAR(17), A.[startTime], 100) as startTime,
           CONVERT(VARCHAR(17), A.[endTime], 100) as endTime,
           A.[whoCreated],
           A.[center],
           B.[FirstName],
           B.[LastName],
           B.[ntid] as empNTID,
           C.[centerName],
           D.[employee],
           E.[segmentID]
   FROM   Focus_Meetings AS A
   JOIN empTable as B
   ON A.[whoCreated] = B.[empID]
   JOIN Focus_Centers as C
   ON A.[center] = C.[id]
   JOIN Focus_Attendees as D
   ON D.[meetingID] = A.[id]
   JOIN Focus_Meetings_Segments as E
   ON E.[meetingID] = A.[id]
   WHERE

        (CAST(A.startTime AS DATE) = CAST(COALESCE(@meetingDate, A.startTime) AS DATE) OR
         CAST(A.endTime AS DATE) = CAST(COALESCE(@meetingDate, A.endTime) AS DATE) OR
         (E.[segmentID] IN( SELECT ParamValues.x2.value('segment[1]', 'INT')
                            FROM   @meetingSegment.nodes('/segments/theSegment') AS ParamValues(x2))
                            )
        )
    FOR    XML PATH ('details'), TYPE, ELEMENTS, ROOT ('root');

There is 1 record in the Focus_Meetings table and 5 records in the Focus_Meetings_Segments.

My result should only be the one meeting but its giving a record for every D.[employee] and E.[segmentID].

I assume that's how its supposed to work with my query but that's not my intent.

There are 5 segments attached to the meeting in the Focus_Meetings_Segments and when I search one of them, it should only by showing me the meeting 1 time, not once for each segment.

Answers


You are correct that this is how your query is supposed to work. This is a common problem that many people new to JOINS run into.

Essentially, you are currently asking SQL Server to return every set of data based on your JOINS and that is what it is doing. It sounds like what you want is for it to arbitrarily drop records from the result set.

Consider the following simplified version of your result set:

Subject   | Description    | SegmentId
-----------------------------------------
Whatever  | Some desc...   | 1
Whatever  | Some desc...   | 2

Based on your description, you only want the Whatever | Some desc... portion of the results to display one time.

If that is what you want to do, you have a couple of options.

  1. Stop selecting the data (SegmentId) that is causing the records to show twice and only select distinct records.

    SELECT DISTINCT Subject, Description...

  2. Specify an aggregate function on the data that is causing records to show twice and group by the rest.

    SELECT Subject, Description, MAX(SegmentId)... GROUP BY Subject, Description

You should also evaluate exactly what you need to select vs. what you are selecting. If you are arbitrarily selecting the SegmentId then you probably don't need it in the first place.


when I search one of them it should only by showing me the meeting 1 time, not once for each segment

Then take Segment and Employee out of th emain query and do a subquery:

SELECT A.[id],
       A.[subject],
       A.[description],
       CONVERT(VARCHAR(17), A.[startTime], 100) as startTime,
       CONVERT(VARCHAR(17), A.[endTime], 100) as endTime,
       A.[whoCreated],
       A.[center],
       B.[FirstName],
       B.[LastName],
       B.[ntid] as empNTID,
       C.[centerName]
FROM   Focus_Meetings AS A
JOIN empTable as B
ON A.[whoCreated] = B.[empID]
JOIN Focus_Centers as C
ON A.[center] = C.[id]
WHERE A.[id] IN
(
    SELECT E.[meetingID]
    FROM Focus_Meetings_Segments as E
    WHERE
        (CAST(A.startTime AS DATE) = CAST(COALESCE(@meetingDate, A.startTime) AS DATE) OR 
         CAST(A.endTime AS DATE) = CAST(COALESCE(@meetingDate, A.endTime) AS DATE) OR
         (E.[segmentID] IN( SELECT ParamValues.x2.value('segment[1]', 'INT')
                        FROM   @meetingSegment.nodes('/segments/theSegment') AS ParamValues(x2))
                        )
         )
)
FOR    XML PATH ('details'), TYPE, ELEMENTS, ROOT ('root');

Need Your Help

For some reason JSP documents output XML instead of HTML

java jsp jsf

OK, so I am trying to set up a simple JSF application. I'm using NetBeans 6.8, Glassfishv3 and Maven2. I made a JSP document like so:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.