Geek asks and answers

Get unlabeled data from db

WHAT AM I DOING:

I am writing a C # application that will help employees process emails. I have a mail database (SQL Server 2008 Express). One table contains the letters themselves (each row has columns, such as msgId, sender, recipients, subject, body, etc.), and the other table contains data on how the letters were processed by employees - whenever a property is assigned to an email, a new row with this the property saves the second table.

The second table contains columns: msgId, forTeam, notForTeam, processedByTeam. Whenever an employee marks a message related to his team, the row is saved with the TEAMXXvalue in the column forTeam. Whenever an employee performs other actions by email, the row with is TEAMXXsaved in the column processedByTeam.

I am trying to display emails in the "Raw" view, i.e. mails that have the following meanings:

forTeam: null
notForTeam: everything different than 'TEAM01'
processedByTeam: everything different than 'TEAM01'

I'm trying to write a lot of different queries, but it still doesn't work the way I want, and it makes me go crazy. We will be grateful for the correct request.

Example:

Table with mail ( Mails):

msgId sender  subject body  received (sortingTime)
53    x@x.com test    test  2012-05-11 11:00
54    b@b.com test2   test2 2012-05-10 10:00
55    h@h.com blah    blah  2012-05-11 12:00
56    t@t.com dfgg    dfgg  2012-05-11 12:30

Table with assignments ( MailAssignments):

msgId forTeam notForTeam  processedByTeam
54    null    TEAM02      null            - this means TEAM02 hid the mail from their view
54    TEAM01  null        null            - this means TEAM01 assigned the mail to them
54    null    null        TEAM01          - TEAM01 has processed the mail

53    TEAM01  null        null            - invisible to other teams
53    null    TEAM01      null            - invisible for TEAM01 (and also for others because of the above)

56    TEAM01  null        null
56    null    null        TEAM01          - mail processed by TEAM01

Now I want my C # application to have a view that will display emails as "Unprocessed", i.e. ..

  • mail 54 TEAM02 -
  • 54 TEAM01 - " "
  • 54 TEAM01 - " "
  • 53 TEAM01 - " " ( ).
  • 53 TEAM01 - TEAM01 ( - - ).
  • 56 TEAM01 - TEAM01 " "
  • 56 TEAM01 - TEAM01 " "

, , , TEAM01 "" :

mail 55
+3
1
DECLARE @teamName varchar(50) = 'TEAM01'

SELECT * 
FROM Mails M
LEFT OUTER JOIN MailAssignments MA ON M.msgId = MA.msgId
WHERE MA.msgId IS NULL OR 
      (
         MA.forTeam IS NULL AND 
         MA.notForTeam <> @teamName AND 
         MA.processedByTeam <> @teamName
      )

, ?

+2

More articles:


All Articles