SQL Query for conditional selecting

Question

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!

Which RDBMS is this for? Please add a tag to specify whether you're using mysql, postgresql, sql-server, oracle or db2 - or something else entirely. — marc_s
– marc_s, Commented Jan 29, 2016 at 6:21

Nir Levy · Accepted Answer · 2016-01-28 21:56:41Z

2

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

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

edited Jan 28, 2016 at 21:56

answered Jan 28, 2016 at 21:54

Nir Levy

13k3 gold badges23 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

AlexB · Accepted Answer · 2016-01-28 21:55:16Z

1

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')

answered Jan 28, 2016 at 21:55

AlexB

3,5684 gold badges33 silver badges47 bronze badges

Comments

wentimo · Accepted Answer · 2016-01-28 21:55:16Z

1

You can do this using inner queries.

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

answered Jan 28, 2016 at 21:55

wentimo

4913 silver badges12 bronze badges

Comments

Timothy Kanski · Accepted Answer · 2016-01-28 23:33:35Z

1

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.

edited Jan 28, 2016 at 23:33

answered Jan 28, 2016 at 22:02

Timothy Kanski

1,88816 silver badges20 bronze badges

3 Comments

wentimo Over a year ago

SELECT 1 will select 1, I suspect you meant SELECT TOP 1?

Timothy Kanski Over a year ago

@wentimo No, I dont mean top 1. The way i wrote it will select 1, yes. but that is enough to locate the row and prove its existence. since we don't need any fields from the Exists clause, using a 1 in place of * makes it runs faster and does the same thing.

wentimo Over a year ago

Ah nifty, I didn't know about this particular pattern as a solution. Thanks for pointing it out.

Milky · Accepted Answer · 2016-01-28 22:08:44Z

0

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.

answered Jan 28, 2016 at 22:08

Milky

3913 silver badges6 bronze badges

Collectives™ on Stack Overflow

SQL Query for conditional selecting

5 Answers 5

Comments

Comments

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related