Using Join to display only records without a corresponding foreign key

Question

I have been working on this all day trying several offers from several sites, and I just can't figure it out!

I use MS SQL and I have 3 SQL tables that have the following columns:

USERS
------
UserID

UserAccounts
------------
UserID
AccountID

Accounts
--------
AccountID

Here is a table chart (ignore data types, only for reference to relationships)

I need to get a list of accounts in which this UserID from the Users table does not have an entry in the UserAccounts table.

The problem I am facing is that many users can be associated with many of the same accounts. So, for example, in the UserAccounts table, this data will be valid:

UserAccounts
------------------
|UserID|AccountID|
------------------
| 1    | 1       |
| 2    | 1       |
| 2    | 3       |

- "IS NULL" "< > @UserID", 2 1, .

? !

:

SELECT        Accounts.ID, Accounts.CompanyAgencyName
FROM            Accounts LEFT OUTER JOIN
                             (SELECT        UserID, AccountID
                               FROM            MyAccount.UserAccounts
                               WHERE        (UserID = @UserID)) AS DerivedUserAccounts ON Accounts.ID = DerivedUserAccounts.AccountID
WHERE        (DerivedUserAccounts.AccountID IS NULL)
ORDER BY Accounts.ID

Answer 1

{1,2,3,4,5}

{10,20,30,40,50}

UserAccount {{1,20}, {1,50}, {2,20}, {3,10}, {4,40}, {4,50}}

Query:

declare @userId int; set @userId = 4;
with cte ( userId, accountId ) as
(
select userId, accountId from userAccount where userId = @userId
)
select a.*
from
    account a
    left join cte b on ( a.Id = b.accountId )
where
    ( b.accountId is null )

:

10,20,30

Answer 2

select * from accounts a where not exists (select * from UserAccounts where AccountID=a.AccountID and UserID=738)

738 - .

Answer 3

create table #user (uid int primary key);
create table #account (aid int primary key);
create table #useraccount (uauid int,uaaid int PRIMARY KEY (uauid,uaaid));
insert into #user VALUES (1),(2),(3),(4);
insert into #account VALUES (1),(2),(3),(4);
insert into #useraccount VALUES (1,1),(2,1),(2,3);
SELECT * FROM #user WHERE NOT EXISTS (SELECT 1 FROM #useraccount WHERE uauid=uid)

. . useraccount account. :

SELECT * FROM #user WHERE NOT EXISTS 
  (SELECT 1 FROM #useraccount JOIN #account ON aid=uaaid WHERE uauid=uid)