CODEWITHSUNDEEP
mysql
amitshree
amitshree

Reputation: 2298

Select customers without address

There are two tables ps_customer and ps_address.

ps_customer contains: id_customer and customer.email
ps_address contains: id_customer and few other fields.

I want to select customers who's data is not present in ps_address table. I'm using Sql Query:

SELECT id_customer, customer.email FROM ps_customer 
WHERE id_customer NOT IN SELECT id_customer FROM ps_address;

But it throws the error that there is something wrong with query SELECT id_customer FROM ps_address

Upvotes: 1

Views: 840

Answers (5)

Jens
Jens

Reputation: 69440

You can use left join for it:

SELECT id_customer, customer.email 
FROM ps_customer 
   left join ps_address on ps_customer.id_customer= ps_address.id_customer 
where ps_address.id_customer is null;

or if you want to use subqueries you have to use () arround the subquery:

SELECT id_customer, customer.email FROM ps_customer 
WHERE id_customer NOT IN ( SELECT id_customer FROM ps_address );

Upvotes: 0

Thanos Markou
Thanos Markou

Reputation: 2624

You need a LEFT OUTER JOIN on those two tables.

SELECT PSC.id_customer, PSCcustomer.email
FROM ps_customer PSC
LEFT OUTER JOIN ps_address PSA on PSC.id_customer = PSA.id_customer 
WHERE PSA.id_customer IS NULL

Upvotes: 1

Rahul Tripathi
Rahul Tripathi

Reputation: 172418

You can try using left join

SELECT id_customer, customer.email FROM ps_customer pc left join ps_address pa
on pc.id_customer = pa.id_customer
where pa.id_customer is null;

Upvotes: 0

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

You can use left join for this something as

select 
c.id_customer,
c.customer.email 
from ps_customer c
left join ps_address a on a.id_customer = c.id_customer
where a.id_customer is null

Upvotes: 0

Bob Tway
Bob Tway

Reputation: 9603

You need to put brackets round the last part of your query to indicate that it's a subquery:

SELECT id_customer, customer.email FROM ps_customer 
WHERE id_customer NOT IN ( SELECT id_customer FROM ps_address );

Upvotes: 0

Related Questions