CODEWITHSUNDEEP
c#sqlms-access
Val
Val

Reputation: 1832

SQL: select data which doesn't have relations in another table

I'm writing a C# application and am using an Access .mdb. I have a table with email messages and a table with message relations (each email msg can be assigned to several teams of workers).

I want to to get messages from the first table which are NOT assigned to any team - ie, either have no entries in the second table or have an empty entry. It works ok with empty entries but doesn't return rows which don't have that assignment entry at all.

I'm using the following query:

SELECT TOP 10 Mails.* 
FROM Mails INNER JOIN MailAssignments ON Mails.msgId = MailAssignments.msgId 
    WHERE (Mails.msgId <= ?)
    AND
    (Mails.msgId NOT IN (SELECT msgId FROM MailAssignments))
    OR (MailAssignments.forTeam IS NULL)
    OR (MailAssignments.forTeam = '')

UPDATE:

I NEED to use some kind of JOIN because on some conditions the query should also return rows having a relation in the other table (eg. when someone wants to display messages from their team AND unassigned messages).

UPDATE:

OK, I guess I can make it simplier by deleting any assignment from the second table so that I don't need to check the empty assignments, only the ones that don't exist at all. But I still need to sometimes show data which is assigned ALONG WITH data which has not been assigned. And I need to build one query for that which will only have different parameters changed :/

UPDATE / SOLUTION:

I did some more adjustments but LEFT JOIN basically did the trick for me! Thanks for that hint and all your help, guys!

Upvotes: 2

Views: 2363

Answers (3)

Adriano Repetti
Adriano Repetti

Reputation: 67118

Because you used the join I guess you need columns from the 2nd table, use an OUTER JOIN instead of INNER JOIN (it returns entries on the table without a match in the join).

If you do not need them I agree with @ypercube.

Upvotes: 1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115600

This should be enough:

SELECT TOP 10 Mails.* 
FROM Mails 
    WHERE (Mails.msgId <= ?)
    AND
    (Mails.msgId NOT IN (SELECT msgId FROM MailAssignments))

Reading your description, it seems tha you may have rows in MailAssignments that are related to Mails but a team has not really be assigned (the forTeam column has an empty string. This is not good design but in this case, use this:

SELECT TOP 10 Mails.* 
FROM Mails 
  LEFT JOIN 
     MailAssignments ON Mails.msgId = MailAssignments.msgId
WHERE (Mails.msgId <= ?)
  AND (  (MailAssignments.forTeam IS NULL)
      OR (MailAssignments.forTeam = '')
      )

Upvotes: 4

Andriy M
Andriy M

Reputation: 77707

Join Mails to the subset of MailAssignments trying to match mails with existing assignments (where forTeam <> '') and picking those where no match has taken place:

SELECT TOP 10
  Mails.*
FROM Mails
  LEFT JOIN MailAssignments ON Mails.msgId = MailAssignments.msgId
    AND MailAssignments.forTeam <> ''
WHERE Mails.msgId <= ?
  AND MailAssignments.msgId IS NULL /* either no match for this mail at all
                                       or no match with an existing assignment
                                       – so, just no match */

Upvotes: 2

Related Questions