LEFT JOIN issue

Code:

---

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

Quote:

---

Originally Posted by selanec

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

---

So use LEFT JOIN instead of INNER JOIN.

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!

Hmm it seems that this time i celebrated too soon :(

Code:

---

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

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

It underlines the code as syntax error :(

Quote:

---

Originally Posted by selanec

It underlines the code as syntax error :(

---

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