CODEWITHSUNDEEP
sql
lollah mullah
lollah mullah

Reputation: 13

qrying DB where two customers in one order

lets say I have two tables, Order order_id (PK) ordered_date

CustomerOrders Customer_order_id (PK) order_id (FK) customer_type(char1) ( can be S, T and M)

If one or more different types of customers involved in an order, the table will look like Order order_id 5 order_date '05-06-2020'

CusotmerOrder customer_order_id 1 order_id 5 type 'M'

customer_order_id 2 order_id 5 type 'S'

and so on

How can I write a qry that will return all unique order_ids that have combination of S and M type customers?

Upvotes: 0

Views: 26

Answers (2)

Leszek Mazur
Leszek Mazur

Reputation: 2531

It is easy self join query:

SELECT DISTINCT M.order_id
FROM CustomerOrders AS M
INNER JOIN CustomerOrders AS S
  ON M.order_id = S.order_id
WHERE M.customer_type = 'M'
  AND S.customer_type = 'S'

Upvotes: 1

Cetin Basoz
Cetin Basoz

Reputation: 23797

You can use exists:

Select distinct order_id 
from CustomerOrder co
where exists (select * from CustomerOrder co1 
   where co.order_id = co1.order_id and co1.Type = 'M') and
exists (select * from CustomerOrder co1 
   where co.order_id = co1.order_id and co1.Type = 'S');

Upvotes: 0

Related Questions