CODEWITHSUNDEEP
sqlsql-server
Hewad
Hewad

Reputation: 23

How to inverse a statement in SQL server

i am designing a stack exchange software and i have this query to retrieve those account name in which my customer has account 

SELECT TOP (200) account.account_no, account_type.account_type_id, account_type.account_name, customer.first_name
FROM account 
     INNER JOIN customer ON account.customer_id = customer.customer_id 
     INNER JOIN account_type ON account.account_type = account_type.account_type_id
WHERE (account.customer_id = 2)

And now I want to select Those accounts in which my customer don't have account

Upvotes: 0

Views: 411

Answers (3)

forpas
forpas

Reputation: 164139

This query:

select t.account_type_id from account a 
inner join account_type t ON a.account_type = t.account_type_id
where a.customer_id = 2

contains all the account_type_ids that you want to exclude.
So use it with a NOT IN clause:

select account_type_id, account_name 
from account_type
where account_type_id not in (
  select t.account_type_id from account a 
  inner join account_type t ON a.account_type = t.account_type_id
  where a.customer_id = 2
)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270463

I would use NOT EXISTS:

SELECT act.*
FROM account_type act
WHERE NOT EXISTS (SELECT 1
                  FROM account a
                  WHERE a.account_type = act.account_type AND
                        a.customer_id = 2
                 );

Note that the customer table is not needed for this.

Upvotes: 0

Barbaros Özhan
Barbaros Özhan

Reputation: 65363

You can use RIGHT JOIN with WHERE account.customer_id is null

SELECT TOP(200) account.account_no,
       account_type.account_type_id,
       account_type.account_name,
       customer.first_name
  FROM account
 RIGHT JOIN customer
    ON account.customer_id = customer.customer_id  
 INNER JOIN account_type
    ON account.account_type = account_type.account_type_id
 WHERE account.customer_id is null

Upvotes: 1

Related Questions