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

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2009-02-04 : 14:53:02

I will try to make myself clearer this time.

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.. if the productID does not exist in the shopping cart, it does not come back in my record set.

Ok visakh16 reccomened the code below. and it always brings back a record if there is a product. If the record was less than 10 minutes old, the DateAdded field is null. The probem is, it brings back a record. I don't want that to happen. If there is no joining record in the shopping cart, I do want it to come back

SELECT product.pantiesSetName, ShoppingCart.DateAdded
FROM product
LEFT OUTER JOIN ShoppingCart
ON product.productID = ShoppingCart.productID
AND ShoppingCart.DateAdded < AND ABS(DATEDIFF(n, ShoppingCart.DateAdded, GETDATE())) > 10
WHERE product.modelID = 1

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

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-04 : 15:06:54

use right outer join instead.

helixpoint
Constraint Violating Yak Guru

291 Posts

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

Won't work. If the item is not in the shopping cart, I get nothing. I want to get a product back if a product is NOT in the shopping cart and also it it is in the shopping cart, I want the product to come back if the record has been in the shopping cart for more than 10 minutes

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

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2009-02-04 : 15:35:13

Try this:


SELECT product.pantiesSetName, ShoppingCart.DateAdded
FROM product 
INNER JOIN ShoppingCart 
	ON product.productID = ShoppingCart.productID
		AND ABS(DATEDIFF(n, ShoppingCart.DateAdded, GETDATE())) > 10
WHERE product.modelID = 1 
UNION
SELECT product.pantiesSetName, ShoppingCart.DateAdded
FROM product 
LEFT JOIN ShoppingCart 
	ON product.productID = ShoppingCart.productID
WHERE product.modelID = 1 AND ShoppingCart.DateAdded IS NULL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-04 : 15:36:55

[code]SET NOCOUNT ON

DECLARE @Now DATETIME

SET @Now = '20090204 21:40'

DECLARE @Cart TABLE
(
ProductID INT NOT NULL,
Added DATETIME NOT NULL
)

INSERT @Cart
SELECT 2, '20090204 21:35' UNION ALL
SELECT 3, '20090204 21:25'

DECLARE @Products TABLE
(
ProductID INT NOT NULL,
ProductName VARCHAR(20) NOT NULL
)

INSERT @Products
SELECT 1, 'Microsoft SQL Server' UNION ALL
SELECT 2, 'SQLTeam' UNION ALL
SELECT 3, 'Peso'

SELECT p.ProductName,
c.Added
FROM @Products AS p
LEFT JOIN @Cart AS c ON c.ProductID = p.ProductID
WHERE c.ProductID IS NULL
OR c.Added <= DATEADD(MINUTE, -10, @Now)[/code]

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-04 : 15:41:17

[code]SELECT p.PantiesSetName,
c.DateAdded
FROM Product AS p
LEFT JOIN ShoppingCart AS c ON c.ProductID = p.ProductID
WHERE p.ModelID = 1
AND (c.ProductID IS NULL OR c.Added <= DATEADD(MINUTE, -10, GETDATE))[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources