CODEWITHSUNDEEP
sql
GIS_DBA
GIS_DBA

Reputation: 221

A simple nested SQL statement

I have a question in SQL that I am trying to solve. I know that the answer is very simple but I just can not get it right. I have two tables, one with customers and the other one with orders. The two tables are connected using customer_id. The question is to list all the customers that did not make any order! The question is to be run in MapInfo Professional, a GIS desktop software, so not every SQL command is applicable to that program. In other words, I will be thankful if I get more than approach to solve that problem.

Here is how I have been thinking:

SELECT customer_id 
from customers
WHERE order_id not in (select order_id from order) 
   and customer.customer_id = order.customer_id

Upvotes: 0

Views: 374

Answers (4)

Xie
Xie

Reputation: 374

There are some problems with your approach:

  1. There is probably no order_id in the customers table, but in your where-statement you refer to it
  2. The alias (or table-name) order in the where-statement (order.customer_id) is not known because there is no join statement in there
  3. If there would be a join, you would filter out all customers without orders, exactly the opposite of what you want

Your question is difficualt to answer to me because I do not know which SQL subset MapInfo GIS understands, but lets try:

select * from customers c where not exists (select * from order o where o.customer_id=c.customer_id)

Upvotes: 0

Ian Kenney
Ian Kenney

Reputation: 6446

... The NOT EXISITS way:

SELECT * FROM customers
WHERE NOT EXISTS ( 
  SELECT * FROM orders
  WHERE orders.customer_id = customer.customer_id
)

Upvotes: 0

a1ex07
a1ex07

Reputation: 37382

SELECT c.customer_id 
FROM customers c
LEFT JOIN orders o ON (o.customer_id = c.customer_id)
WHERE o.order_id IS NULL

Upvotes: 0

Juned Ahsan
Juned Ahsan

Reputation: 68715

How about this:

SELECT * from customers
WHERE customer_id not in (select customer_id from order)

The logic is, if we don't have a customer_id in order that means that customer has never placed an order. As you have mentioned that customer_id is the common key, hence above query should fetch the desired result.

Upvotes: 2

Related Questions