Customers who ordered two products

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Customers who ordered two products

Author

Topic

mapidea
Posting Yak Master

124 Posts

Posted - 2009-02-11 : 01:26:24

I wrote a query for the following

“Find all the customers who ordered a Product 1 and also ordered a Product 2.”

select a.*
from customers as a
inner join orders as b on a.customerid = b.customerid
inner join orderdetails as c on b.orderid = c.orderid
inner join products as d on c.productid = d.productid
where d.productname = 'Product Name 2'
And b.orderid in
(
select e.orderid
from orderdetails as e
inner join products as f on e.productid = f.productid
where f.productname = 'Product Name 1'
)

Is there is a simplified way to write this.

Thanks

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-11 : 01:34:57

try like this
select a.*
from customers as a
inner join orders as b on a.customerid = b.customerid
inner join orderdetails as c on b.orderid = c.orderid
inner join products as d on c.productid = d.productid
where d.productname in('Product Name 2' ,'Product Name 1' )

mapidea
Posting Yak Master

124 Posts

Posted - 2009-02-11 : 01:50:03

Your query will give all the customers who bought either Product 1 or Product 2.

I need all the customers who bought Product 1 and also bought product 2.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-11 : 01:59:47

use this in where clause
d.productname ='Product Name 1' and d.productname ='Product Name 2'

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-02-11 : 02:06:39

[code]select
a.*
from
customers as a
inner join orders as b on a.customerid = b.customerid
inner join orderdetails as c on b.orderid = c.orderid
inner join products as d on c.productid = d.productid and d.productname = 'Product Name 1'
inner join products as e on c.productid = e.productid and e.productname = 'Product Name 2'[/code]

mapidea
Posting Yak Master

124 Posts

Posted - 2009-02-11 : 02:20:54

Thanks for your reply. The above queries will check if the Product Name is "Product Name 1" and also "Product Name 2" which will never be true.

Another way of writing my query can be

select * from
(
select a.*
from customers as a
inner join orders as b on a.customerid = b.customerid
inner join orderdetails as c on b.orderid = c.orderid
inner join products as d on c.productid = d.productid
where d.productname = 'Product Name 2'
) as o
Inner join
(
select a.*
from customers as a
inner join orders as b on a.customerid = b.customerid
inner join orderdetails as c on b.orderid = c.orderid
inner join products as d on c.productid = d.productid
where d.productname = 'Product Name 1'
)
as p
on o.customerid = p.customerid

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-11 : 02:32:54

quote:
Originally posted by mapidea

Find all the customers who ordered a Product 1 and also ordered a Product 2.

In same unique order, or in total orders?

E 12°55'05.63"
N 56°04'39.26"

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-02-11 : 02:35:01

You are right in that, the query I wrote will return any rows only if the customer placed both products in a single order. What you have written, or this:

select 
   a.* 
from 
   customers as a
   inner join orders as b on a.customerid = b.customerid
   inner join orderdetails as c on b.orderid = c.orderid
   inner join products as d on c.productid = d.productid and d.productname = 'Product Name 1'
   inner join orders as b2 on a.customerid = b2.customerid
   inner join orderdetails as c2 on b2.orderid = c2.orderid
   inner join products as d2 on c2.productid = d2.productid and d2.productname = 'Product Name 2'

mapidea
Posting Yak Master

124 Posts

Posted - 2009-02-11 : 03:02:03

Thanks Peso. If we need the customers who bought the product 1 and 2 in the same order then the following query will hold

select a.*
from customers as a
inner join orders as b on a.customerid = b.customerid
inner join orderdetails as c on b.orderid = c.orderid
inner join products as d on c.productid = d.productid
where d.productname = 'Product Name 2'
And b.orderid in
(
select e.orderid
from orderdetails as e
inner join products as f on e.productid = f.productid
where f.productname = 'Product Name 1'
)

If we need customers who ordered product 1 and 2 in history of their purchase then the query given sunitabeck and the below query will hold

select * from
(
select a.*
from customers as a
inner join orders as b on a.customerid = b.customerid
inner join orderdetails as c on b.orderid = c.orderid
inner join products as d on c.productid = d.productid
where d.productname = 'Product Name 2'
) as o
Inner join
(
select a.*
from customers as a
inner join orders as b on a.customerid = b.customerid
inner join orderdetails as c on b.orderid = c.orderid
inner join products as d on c.productid = d.productid
where d.productname = 'Product Name 1'
)
as p
on o.customerid = p.customerid

Thanks everyone for reply it is much clear now.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-11 : 03:07:17

[code]SELECT DISTINCT b.customerid
FROM orders AS b
INNER JOIN orderdetails AS c ON b.orderid = c.orderid
INNER JOIN products AS d ON c.productid = d.productid
WHERE d.productname IN ('Product Name 1', 'Product Name 2')
GROUP BY b.customerid,
b.orderid
HAVING COUNT(DISTINCT d.productname) = 2

SELECT b.customerid
FROM orders AS b
INNER JOIN orderdetails AS c ON b.orderid = c.orderid
INNER JOIN products AS d ON c.productid = d.productid
WHERE d.productname IN ('Product Name 1', 'Product Name 2')
GROUP BY b.customerid
HAVING COUNT(DISTINCT d.productname) = 2[/code]

E 12°55'05.63"
N 56°04'39.26"

mapidea
Posting Yak Master

124 Posts

Posted - 2009-02-11 : 03:46:09

Thanks. You made it so simple.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources