LEFT JOIN issue

[selanec]

SELECT 
		Ord.orderID, Ord.DateCreated, Ord.DateShipped, 
		Ord.OrderTotal, Ord.Subtotal, Ref.UrlReferrer AS Referrer
	FROM 
		customer_Orders AS Ord
	INNER JOIN
		customer_WebStatistics AS Ref
	ON
		Ord.IPAddress = Ref.IPAddress
	WHERE
		(Ord.DateShipped >= @StartDate AND Ord.DateShipped <= @EndDate)
	AND 
		Ord.IPAddress 
	IN 
		(
			SELECT 
				IPAddress 
			FROM 
				customer_WebStatistics 
			WHERE 
				UrlReferrer <> @CurrentDomain 
			AND 
				Ref.EnterDate = Ord.DateCreated
		)

I am aware that this will not work as expected. However i wanted to give you an idea of what i am trying to achive here.


Basically i want to returns all orders no matter if there is Referrer or NO.

The referrer should be included ONLY if it happened on the same date with the order AND if UrlReferrer contains the Request.ServerVariables("SERVER_NAME").

Thanks for any ideas

[Pradeep1210]

Basically i want to returns all orders no matter if there is Referrer or NO.

So use LEFT JOIN instead of INNER JOIN.

[selanec]

Yeah i just recalled that you advised me the same some time ago. I have asked very similar question and you also told me to use LEFT JOIN. But, i have forgotten it unfortunately.

Thank you again. It is much appreciated!

[selanec]

Hmm it seems that this time i celebrated too soon

CREATE PROCEDURE SalesReport
	(
		@Store int,
		@StartDate datetime,
		@EndDate datetime,
		@Domain varchar(1024)
	)
AS
	SET NOCOUNT ON 
	
	DECLARE @IPAddress varchar(20);
	
	SELECT 
		Ord.order_ID, Ord.DateCreated, Ord.DateShipped, 
		Ord.OrderTotal, Ord.Subtotall, Ord.StoreStatus, 
		Ord.StoreProfit, Ord.OrderStatus, Ref.UrlReferrer AS Referrer
	FROM 
		customer_Orders AS Ord
	LEFT OUTER JOIN
		customer_TrafficStatistics AS Ref
	ON
		Ord.IPAddress = Ref.IPAddress
	WHERE
		Ord.StoreOwner = @Store
	AND
		Ord.OrderCurrentStatus <> 4 
	AND 
		(Ord.StoreStatus <> 3 AND Ord.StoreStatus <> 6)
	AND
		(Ord.DateShipped >= @StartDate AND Ord.DateShipped <= @EndDate)
	AND 
		Ord.StoreOwner = @Store
	AND
		Ref.UrlReferrer <> @Domain 
	AND 
		DATEPART(day, Ord.DateCreated) = DATEPART(day, Ref.EnterDate)

If you throw out the last two conditions it works like a charm but AS IS it does not return anything. return 0


Any ideas? Thanks

[Pradeep1210]

What happens if you substitute "WHERE" with "AND" ?

[selanec]

It underlines the code as syntax error

[Pradeep1210]

It underlines the code as syntax error

It doesn't compile? Or you get errors when executing the stored procedure?
what is the error?