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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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

201 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  
 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.08 seconds. Powered By: Snitz Forums 2000