Duplicate Rows with desired columns

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)

Duplicate Rows with desired columns

Author

Topic

dhoom
Starting Member

9 Posts

Posted - 2008-10-22 : 01:54:05

Hi,

I want all rows which hv a duplicate inner join on ProductPriceHistory.ProductID. My tables are below:

Product
-------
ProductID (PK, int)
Category

ProductPriceHistory
-------------------
PriceHistoryID (PK, int)
ProductID (FK, int)
SDate
UPrice

When I run this query it shows the right results:

SELECT Product.ProductID, Product.Category, COUNT(*) AS NoOfOccurences
FROM Product INNER JOIN ProductPriceHistory ON Product.ProductID = ProductPriceHistory.ProductID
group BY Product.ProductID, Product.Category
HAVING (COUNT(*) > 1)

But when i add columns from other table it doesnt show any results, but i require to display other columns too. I used this query below: No results. No Error.

SELECT Product.ProductID, Product.Category, ProductPriceHistory.StartDate, ProductPriceHistory.UnitPrice, COUNT(*) AS NoOfOccurences
FROM Product INNER JOIN ProductPriceHistory ON Product.ProductID = ProductPriceHistory.ProductID
group BY Product.ProductID, Product.Category, ProductPriceHistory.StartDate, ProductPriceHistory.UnitPrice
HAVING (COUNT(*) > 1)

Can you tell me what i may be missing?

Thanks,
~Dhoom.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-22 : 02:03:11

[code]SELECT p.ProductID, p.Category, tmp.StartDate, tmp.UnitPrice,tmp.count
FROM Product p
INNER JOIN (SELECT ph.*,ph1.count
FROM ProductPriceHistory ph
INNER JOIN (SELECT ProductID,Count(*) AS count
FROM ProductPriceHistory
GROUP BY ProductID
HAVING COUNT(*) >1)ph1
ON ph.ProductID=ph1.ProductID)tmp
ON tmp.ProductID=p.ProductID[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-22 : 02:09:14

just noticed you've posted this in 2005 forum. in which case you can use this specific solution also

SELECT *
FROM
(
SELECT *,count(PriceHistoryID) over (partition by ProductID) as totalprdtcount
FROM Product p
INNER JOIN ProductPriceHistory ph
ON ph.ProductID=p.ProductID
)t
WHERE totalprdtcount>1

dhoom
Starting Member

9 Posts

Posted - 2008-10-22 : 02:14:53

Thanks Visakh16
~Dhoom

dhoom
Starting Member

9 Posts

Posted - 2008-10-22 : 02:21:22

quote:
Originally posted by visakh16

just noticed you've posted this in 2005 forum. in which case you can use this specific solution also
SELECT *
FROM
(
SELECT *,count(PriceHistoryID) over (partition by ProductID) as totalprdtcount
FROM Product p
INNER JOIN ProductPriceHistory ph
ON ph.ProductID=p.ProductID
)t
WHERE totalprdtcount>1

I am getting an error when i run the above query.
-Ambiguous column name 'ProductID'.
-The column 'ProductID' was specified multiple times for 't'.

But the earlier one works fine!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-22 : 02:25:03

thats because you've same column in both tables. replace * with actual column names.

SELECT *
FROM
(
SELECT p.ProductID, p.Category, ph.StartDate, ph.UnitPrice,count(ph.PriceHistoryID) over (partition by p.ProductID) as totalprdtcount
FROM Product p
INNER JOIN ProductPriceHistory ph
ON ph.ProductID=p.ProductID
)t
WHERE totalprdtcount>1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources