CODEWITHSUNDEEP
sql
user5004724
user5004724

Reputation: 61

SQL Query for conditional selecting

I have a question about how to retrieve data from one customer's order table (Oracle) like this:

id   customer cake          top   
1    001      chocolate     cream
2    002      chocolate     jam
3    002      vanilla       cream
4    003      banana        cream

I want to know all orders from customers whose order includes chocolate cake.

The right result should be 

id   customer cake          top   
1    001      chocolate     cream
2    002      chocolate     jam
3    002      vanilla       cream

I could do this by 2 separate queries in SQL, but is there any way to do this in a single query?

Many Thanks!

Upvotes: 2

Views: 52

Answers (5)

Timothy Kanski
Timothy Kanski

Reputation: 1888

Something like this should work:

SELECT *
FROM customers
WHERE EXISTS (SELECT 1
              FROM order_details
              WHERE customers.id = order_details.customer_id
              AND cake = 'chocolate');

Edited: added the chocolate cake condition.

Upvotes: 1

Milky
Milky

Reputation: 391

SELECT * FROM ORDER O1 WHERE EXISTS (
    SELECT NULL FROM ORDER O2 WHERE O1.ID = O2.ID AND cake = 'chocolate'
 )

You may try a variation of this subquery.

Upvotes: 0

Nir Levy
Nir Levy

Reputation: 12953

you should use an sub-query to get the customers:

select * from order where customer in (
  select distinct customer from order where cake = 'chocolate'
)

Upvotes: 2

wentimo
wentimo

Reputation: 491

You can do this using inner queries.

SELECT * FROM ORDER WHERE CUSTOMER IN (
    SELECT CUSTOMER FROM ORDER WHERE cake = 'chocolate'
 )

Upvotes: 1

AlexB
AlexB

Reputation: 3548

A subquery that selects the distinct customers whose order include chocolate would work : 

select 
  * 
from 
  order 
where 
  customer 
in (select 
      distinct customer 
    from 
      order 
    where 
      cake = 'chocolate')

Upvotes: 1

Related Questions