CODEWITHSUNDEEP
sql-servert-sqlstored-proceduressql-server-2014
diiN__________
diiN__________

Reputation: 7666

Join two fields from same table with parameter

I have two table like this:

tabSubject

+-----------+--------------------+--------------------+----------------------+
| SubjectId | SubjectDescription | MeetingIdImportant | MeetingIdUnimportant |
+-----------+--------------------+--------------------+----------------------+
|    INT    |    NVARCHAR(100)   |      INT NULL      |       INT NULL       |
+-----------+--------------------+--------------------+----------------------+

tabMeeting

+-----------+-------------+
| MeetingId | MeetingDate |
+-----------+-------------+
|    INT    |  DATETIME   |
+-----------+-------------+

In my application I have a filter (dropdownlist with SubjectIds) that calls a stored procedure.

The stored procedure is used to show data in a grid like this:

+-------------+------------------------------+--------------------------------+
| MeetingDate | Number of important subjects | Number of unimportant subjects |
+-------------+------------------------------+--------------------------------+
| 01.05.2016  |              5               |               3                |
+-------------+------------------------------+--------------------------------+

If no subject is chosen, all possible meeting dates have to be shown (even if they don't have any assigned subject yet). If a subject is chosen, only those meetings should appear that have this subject as either important or unimportant subject.

Example:

I chose subject "Testing". This subject is important in the meeting with the date 04.05.2016 and unimportant in the meeting with the date 11.05.2016. Now I expect a result like this:

+-------------+------------------------------+--------------------------------+
| MeetingDate | Number of important subjects | Number of unimportant subjects |
+-------------+------------------------------+--------------------------------+
| 04.05.2016  |              6               |               2                |
| 11.05.2016  |              2               |               4                |
+-------------+------------------------------+--------------------------------+

This is working with this code in my stored procedure:

SELECT meeting.MeetingId,
    meeting.MeetingDate,
    (SELECT COUNT(MeetingIdMS1) FROM dbo.tabSubject WHERE MeetingIdImportant = meeting.MeetingId) AS NumberOfImportantSubjects,
    (SELECT COUNT(MeetingIdMS8) FROM dbo.tabSubject WHERE MeetingIdUnimportant = meeting.MeetingId) AS NumberOfUnimportantSubjects
FROM dbo.tabMeeting meeting
INNER JOIN
(
    SELECT MeetingIdImportant MeetingId
    FROM dbo.tabSubject
    WHERE (@SubjectId IS NOT NULL AND SubjectId = @SubjectId)
    UNION ALL
    SELECT MeetingIdUnimportant
    FROM dbo.tabSubject
    WHERE (@SubjectId IS NOT NULL AND SubjectId = @SubjectId)
) t ON
    CASE
        WHEN @SubjectId IS NOT NULL THEN t.MeetingId
        ELSE meeting.MeetingId
    END = meeting.MeetingId

But when I don't chose any subject, no data is shown. What am I missing out?

Upvotes: 0

Views: 36

Answers (1)

Eric
Eric

Reputation: 5743

The table t has no data if @SubjectId is null and INNER JOIN returns no data

SELECT meeting.MeetingId,
    meeting.MeetingDate,
    (SELECT COUNT(MeetingIdMS1) FROM dbo.tabSubject WHERE MeetingIdImportant = meeting.MeetingId) AS NumberOfImportantSubjects,
    (SELECT COUNT(MeetingIdMS8) FROM dbo.tabSubject WHERE MeetingIdUnimportant = meeting.MeetingId) AS NumberOfUnimportantSubjects
FROM dbo.tabMeeting meeting
WHERE 
    @SubjectId IS NULL 
    OR EXISTS
    (
        SELECT * FROM tabSubject 
        WHERE 
        (
            MeetingIdImportant = meeting.MeetingId 
            OR MeetingIdUnimportant = meeting.MeetingId
        ) AND SubjectId = @SubjectId
    )

Upvotes: 1

Related Questions