SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query to list duplicates from 2 tables-Please help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sushmanem
Starting Member

Australia
2 Posts

Posted - 10/27/2013 :  20:20:56  Show Profile  Reply with Quote
I have 2 tables - Orders and Order_details
Orders table has customer_id and order_id
Order_details has order_id and product_id.

I want to list all customers that have ordered same product through different orders.
SOmething like this -

customer_id Product_id Order_id
1 10 100,110
2 10 121,131
3 20 115,119

Query I have only lists the number of orders but not the actual orders. In Oracle, group_concat function lists out the orders but cant get it in sql. Can anyone pleaseee help me?

I tried using stuff function but then it is asking me to include the order_id column in group function which then does not result any data

SELECT d.customer_id
,d.product_id
, COUNT(DISTINCT d.orderid) repeated_orders

--GROUP_CONCAT(DISTINCT d.orderid)
--stuff((select ','+ orderid from SolPot.dbo.TblRequests where orderid=d.orderid FOR XML PATH('')) , 1 , 1,'')

FROM (
SELECT c.customer_id
,o.product_id,
o.orderid

FROM orders c
JOIN order_details o ON (c.orderid= o.orderid)

) d

GROUP BY d.customer_id
,d.product_id
HAVING COUNT(DISTINCT d.orderid) > 1;

rocknpop
Posting Yak Master

169 Posts

Posted - 10/28/2013 :  02:53:24  Show Profile  Reply with Quote
DECLARE @ORDERS TABLE(CUSTID INT, ORDERID INT)
INSERT INTO @ORDERS VALUES (1,100),(1,110),(2,121),(2, 131),(3,115),(3,119)

DECLARE @ORDER_DETAILS TABLE(ORDERID INT, PRODUCTID INT)
INSERT INTO @ORDER_DETAILS VALUES (100,10),(110,10),(121,10),(131,10),(115,20),(119,20)

--SELECT * FROM @ORDERS
--SELECT * FROM @ORDER_DETAILS

;WITH CTE
AS(
SELECT O.CUSTID, D.PRODUCTID, O.ORDERID
FROM @ORDERS O INNER JOIN @ORDER_DETAILS D ON O.ORDERID = D.ORDERID
)


SELECT DISTINCT CUSTID, PRODUCTID, STUFF
(
(SELECT ',' + CAST(ORDERID AS VARCHAR)
FROM CTE
WHERE CUSTID=C.CUSTID
FOR XML PATH(''))
, 1, 1, '')
FROM CTE C

--------------------
Rock n Roll with SQL
Go to Top of Page

sushmanem
Starting Member

Australia
2 Posts

Posted - 10/28/2013 :  05:56:33  Show Profile  Reply with Quote
Thank you very much.. Much appreciated.

quote:
Originally posted by rocknpop

DECLARE @ORDERS TABLE(CUSTID INT, ORDERID INT)
INSERT INTO @ORDERS VALUES (1,100),(1,110),(2,121),(2, 131),(3,115),(3,119)

DECLARE @ORDER_DETAILS TABLE(ORDERID INT, PRODUCTID INT)
INSERT INTO @ORDER_DETAILS VALUES (100,10),(110,10),(121,10),(131,10),(115,20),(119,20)

--SELECT * FROM @ORDERS
--SELECT * FROM @ORDER_DETAILS

;WITH CTE
AS(
SELECT O.CUSTID, D.PRODUCTID, O.ORDERID
FROM @ORDERS O INNER JOIN @ORDER_DETAILS D ON O.ORDERID = D.ORDERID
)


SELECT DISTINCT CUSTID, PRODUCTID, STUFF
(
(SELECT ',' + CAST(ORDERID AS VARCHAR)
FROM CTE
WHERE CUSTID=C.CUSTID
FOR XML PATH(''))
, 1, 1, '')
FROM CTE C

--------------------
Rock n Roll with SQL

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000