Help with Select query - SQL Server Forums

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)

Help with Select query

Author

Topic

smh
Yak Posting Veteran

94 Posts

Posted - 2008-01-22 : 16:03:51

I have a product table and a product history table. They are joined on productID. The pricehistory table has productID, customerID, salesdate, salesprice

I want to show the productname, productID from the product table and the salesdate, salespricefrom the producthistory table for a particular customerID. (there is only one price history record for a customer in the table)

The results should be all of the products not just those with a record for the particular custID in the product history table. I thought a outer join would work but it doesnt. HEre is what did not work.

SELECT tblProduct.ProductID, tblProduct.ProductName, tblPriceHistory.SalesPrice,
tblPriceHistory.SalesDate
FROM tblPriceHistory RIGHT OUTER JOIN
tblProduct ON tblPriceHistory.ProductID = tblProduct.ProductID
WHERE (tblPriceHistory.CustomerID = 1)

Thanks for help on this. I am sure this is a common situation but I was not able to find a sample of this.

nathans
Aged Yak Warrior

938 Posts

Posted - 2008-01-22 : 16:10:31

Move the CustomerID into the join condition:


...
from	tblProduct p
left
join	tblPriceHistory ph ON 
	p.ProductID = ph.ProductID and
	ph.CustomerID = 1

Nathan Skerl

nathans
Aged Yak Warrior

938 Posts

Posted - 2008-01-22 : 16:13:07

If im off, modify this to illustrate what you need:


declare @tblProduct table (ProductID int, ProductName varchar(10))
declare @tblPriceHistory table (ProductID int, CustomerID int, SalesPrice int, SalesDate datetime)

insert into @tblProduct
	select 1, 'Hat' union
	select 2, 'Shirt'
insert into @tblPriceHistory
	select 1, 1, 5, getdate()

SELECT	p.ProductID, 
	p.ProductName, 
	ph.SalesPrice,
	ph.SalesDate
from	@tblProduct p
left
join	@tblPriceHistory ph ON 
	p.ProductID = ph.ProductID and
	ph.CustomerID = 1

Nathan Skerl

smh
Yak Posting Veteran

94 Posts

Posted - 2008-01-22 : 17:02:14

Hi
this works:

sELECT p.ProductID, ph.SalesPrice
from tblProduct p
left
join tblPriceHistory ph ON
p.ProductID = ph.ProductID and
ph.CustomerID = 1

Thanks so much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources