need help - 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)

need help

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2009-02-04 : 11:47:07

I have 2 tables. One is a product table the other is a shoppingcart. When I am showing the item on the product page I have to check the shoping cart to see it the item is in the shoppingcart table and I show the item if the DateAdded date was put in there more than 10 minutes ago. The issue is.. with my select below, if the productID does not exist in the shopping cart, it does not come back in my record set.

SELECT product.pantiesSetName, ShoppingCart.DateAdded
FROM product LEFT OUTER JOIN
ShoppingCart ON product.productID = ShoppingCart.productID
WHERE (product.modelID = 1) AND (ShoppingCart.DateAdded < DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 2, 0))

Dave
Helixpoint Web Development
http://www.helixpoint.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-04 : 12:04:06

it should be this i guess


SELECT product.pantiesSetName, ShoppingCart.DateAdded
FROM product 
LEFT OUTER JOIN ShoppingCart 
ON product.productID = ShoppingCart.productID
AND ShoppingCart.DateAdded < DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 2, 0)
WHERE product.modelID = 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-04 : 12:05:08

the reason is this

http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/60205.aspx

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2009-02-04 : 12:15:26

Thanx man. You save me time and time again. Much appreciated!!

Dave
Helixpoint Web Development
http://www.helixpoint.com

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-02-04 : 12:15:44

wow nice article
LOVE IT!! Excellent

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-04 : 12:17:05

Yup..it really is
Thanks to Jeff

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-04 : 12:17:38

quote:
Originally posted by helixpoint

Thanx man. You save me time and time again. Much appreciated!!

Dave
Helixpoint Web Development
http://www.helixpoint.com

you're welcome

glad that i could help you out

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2009-02-04 : 13:41:06

There is a problem... When I add AND ABS(DATEDIFF(n, ShoppingCart.DateAdded, GETDATE())) > 10
and there is a record in the shopping cart, I get a null in the DateAdded

Dave
Helixpoint Web Development
http://www.helixpoint.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources