Possible double nested sql query?

Question

I'm attempting to create a nested (and it must be nested) SQL query to produce the following results:

"Get all customers who have the same discount as that of any customers in Dallas or London".

Here is the database with four tables:

DATABASE CAP 2:
Customers
cid  name           city   discount
c001 Tiptop         Duluth  10.00
c002 Basics         Dallas  12.00
c003 Allied         Dallas   8.00
c004 ACME           Duluth   8.00
c005 WeylandYutani Acheron  0.00
c006 ACME           Kyoto    0.00

Orders
ordno mon  cid   aid  pid   qty  dollars
1011  jan  c001  a01  p01  1000   450.00
1013  jan  c002  a03  p03  1000   880.00
1015  jan  c003  a03  p05  1200  1104.00
1016  jan  c006  a01  p01  1000   500.00
1017  feb  c001  a06  p03   600   540.00
1018  feb  c001  a03  p04   600   540.00
1019  feb  c001  a02  p02   400   180.00
1020  feb  c006  a03  p07   600   600.00
1021  feb  c004  a06  p01  1000   460.00
1022  mar  c001  a05  p06   400   720.00
1023  mar  c001  a04  p05   500   450.00
1024  mar  c006  a06  p01   800   400.00
1025  apr  c001  a05  p07   800   720.00
1026  may  c002  a05  p03   800   740.00

Agents
aid  name   city     percent
a01  Smith  New York     6
a02  Jones  Newark       6
a03  Brown  Tokyo        7
a04  Gray   New York     6
a05  Otasi  Duluth       5
a06  Smith  Dallas       5
a08  Bond   London       7

Products
pid  name   city   quantity priceUSD
p01  comb   Dallas  111400   0.50
p02  brush  Newark  203000   0.50
p03  razor  Duluth  150600   1.00
p04  pen    Duluth  125300   1.00
p05  pencil Dallas  221400   1.00
p06  folder Dallas  123100   2.00
p07  case   Newark  100500   1.00
p08  clip   Newark  200600   1.25

What I'm having trouble with, is being able to grab customers who have equal discounts, but how do I do that? Any help would be appreciated.

This is what I have so far:

SELECT name FROM customers WHERE cid in (
     SELECT cid from customers where discounts in...

Any help would be appreciated.

Please don't link to other websites for important question information. The idea of Stackoverflow is to allow for others in the future to also use the answers here to find solutions to their own problems. They can't do that if important information is at a link that is dead. — Tom H
– Tom H, Commented Feb 18, 2016 at 3:37
Sorry, still kind of new here. I'll edit the post with the information in the question — motoko96
– motoko96, Commented Feb 18, 2016 at 3:42
Maybe some sample data and desired output would help? I think I know what you're asking, but I'm making assumptions that are not clear in your question. Data speaks louder than words at times — Hambone
– Hambone, Commented Feb 18, 2016 at 3:46
For example, an earlier request needed the ids of customers who ordered two products: p01 and p07. The query was SELECT DISTINCT cid from orders WHERE cid in ( SELECT cid from orders WHERE pid='p01' AND cid in ( SELECT cid FROM orders WHERE pid='p07') ); With a result of cid (character 4) c001 c006 or the first and sixth customer meeting the requirements — motoko96
– motoko96, Commented Feb 18, 2016 at 3:55
Could you just do: select * from customers where discount in (select discount from customers where city in ('Dallas', 'London'))? An example is here: sqlfiddle.com/#!9/055d7/4. Is that what you want? — zedfoxus
– zedfoxus, Commented Feb 18, 2016 at 4:10

zedfoxus · Accepted Answer · 2016-02-18 16:25:10Z

1

Just adding some clarification based on my comments.

Use the query like so:

select * 
from customers
where discount in (
  select DISTINCT discount 
  from customers 
  where city in ('Dallas', 'London')
)
and city not in ('Dallas', 'London');

Example: http://sqlfiddle.com/#!9/055d7/4

Let's break this down (Note: I added discount). The inner query select DISTINCT discount from customers where city in ('Dallas', 'London') gives us the discounts customers from Dallas and London receive. We just choose the distinct information.

Then, we ask for all records from customers who aren't in Dallas and London. Additionally, we just want those records where the discounts match from the inner query.

answered Feb 18, 2016 at 16:25

zedfoxus

37.4k5 gold badges68 silver badges66 bronze badges

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

Comments

nshoute · Accepted Answer · 2016-02-18 04:32:56Z

1

Just get the discounts from all the customers in those cities and select all customers with the same discount.

SELECT * 
FROM Customers
WHERE discount in (SELECT discount FROM Customers WHERE city in ("Edmonton", "Calgary"));

Shout out to Alberta.

answered Feb 18, 2016 at 4:32

nshoute

2371 silver badge3 bronze badges

1 Comment

user330315 Over a year ago

String values need to be enclosed in single quotes in SQL, double quotes are for column and table names. "Edmonton" references a column, not a constant value. You need to use 'Edmonton'

Collectives™ on Stack Overflow

Possible double nested sql query?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related