Query to list duplicates from 2 tables-Please help

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 2008 Forums

Transact-SQL (2008)

Query to list duplicates from 2 tables-Please help

Author

Topic

sushmanem
Starting Member

2 Posts

Posted - 2013-10-27 : 20:20:56

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

201 Posts

Posted - 2013-10-28 : 02:53:24

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

sushmanem
Starting Member

2 Posts

Posted - 2013-10-28 : 05:56:33

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources