How to select values where the values do not already exist in a table

Question

Hi!

I have the following SQL Select query, but I only want to select the values that do not already exist in the table usergroupaccount:

SELECT ug.UserGroupId, a.sAccountCode from usergroup ug 
JOIN usergroupaccount uga ON ug.usergroupid = uga.usergroupid
JOIN dbo.tblAccount a ON uga.AccountCode = a.sAccountCode
WHERE ug.code = 'AAA'

Any idea how to exclude records that already exist in usergroupaccount?

Thanks,

Zoe

Answer 1

See if this helps

WITH cte
AS
(
SELECT ug.UserGroupId, a.sAccountCode from usergroup ug
JOIN dbo.tblAccount a ON uga.AccountCode = a.sAccountCode
WHERE ug.code = 'AAA'
) SELECT * FROM usergroupaccount uga WHERE NOT EXISTS (SELECT * FROM cte WHERE
ON CTE.usergroupid = uga.usergroupid)

Answer 2

Something like this?

SELECT *
from usergroup ug 
WHERE ug.code = 'AAA'
      AND NOT EXISTS (SELECT 1 FROM usergroupaccount uga WHERE ug.usergroupid = uga.usergroupid)

Answer 3

 SELECT ug.UserGroupId, a.sAccountCode from usergroup ug 

 INNER JOIN (SELECT usergroupid FROM usergroup EXCEPT SELECT usergroupid FROM usergroupaccount) AS F ON F.usergroupid = ug.usergroupid

 JOIN usergroupaccount uga ON ug.usergroupid = uga.usergroupid
 JOIN dbo.tblAccount a ON uga.AccountCode = a.sAccountCode
 WHERE ug.code = 'AAA'

Answer 4

Hi @zoe Ohara ,

Generally, if you want to return records that do not exist in another table,please try:

        SELECT UserGroupId   
        FROM usergroup   
        WHERE code = 'AAA' and usergroupid not in (select usergroupid from usergroupaccount)  

But I see that you want to return the records of two tables, and according to your code, it seems that the first table is related to the second table, and the second table is related to the third table.If follow your join method, you will not be able to return records that do not exist in the second table, because join will return the data that meets the on condition in the second table.If the first table and the third table are also related, then the problem is easy：

 SELECT ug.UserGroupId, a.sAccountCode   
 FROM usergroup ug   
 JOIN dbo.tblAccount a   
 ON ug.XXX = a.XXX  
 WHERE ug.code = 'AAA' and usergroupid not in (select usergroupid from usergroupaccount)  

If the first table and the third table are not related, then the records that do not exist in the second table, there will be no relevant information in the third table, you only need to follow the first method to return the results you expect:

SELECT UserGroupId   
            FROM usergroup   
            WHERE code = 'AAA' and usergroupid not in (select usergroupid from usergroupaccount)  

Exists and in have similar functions,for more details, please refer to：EXISTS (Transact-SQL) and IN (Transact-SQL)

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Best Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

Hi @zoe Ohara ,

Do you have any updates？
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Thank you for understanding!

Echo