CODEWITHSUNDEEP
sqlsql-serverjoin
NomNomNom
NomNomNom

Reputation: 871

how to retrieve data from a table which has no data from left table?

I have 2 tables: user and address

I want to query and show all users from table user which have no associated address'.

My query is below, but it does not work probably due to having clause. It throws an error instead.

select * from user_application
join user_address on user_address.user_id = user_application.user_id
where address_type = 0
having count(user_address.user_id) = 0

Upvotes: 1

Views: 107

Answers (2)

DhruvJoshi
DhruvJoshi

Reputation: 17126

Approach: Using LEFT JOIN with NULL check

select * from user_application
left join user_address on user_address.user_id = user_application.user_id
and  address_type = 0 where user_address.userid is NULL

Explanation:

We figure out a set of all users with their address information added due to LEFT JOIN and then by using WHERE clause we filter out to desired set of records of user who do not have address

On why your query did not work, because you used a having without group by. Corrected syntax is 

select 
  user_application.user_id -- you can only select the column used in group by 
 from user_application
 join user_address on user_address.user_id = user_application.user_id
where address_type = 0
group by user_application.user_id
having sum(case when user_address.user_id is null then 0 else 1 end) = 0

Upvotes: 3

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can use NOT EXISTS:

select * 
from user_application as u1
where not exists (select 1
                  from user_address as u2
                  where u2.user_id = u1.user_id)

Upvotes: 3

Related Questions