CODEWITHSUNDEEP
sqlinner-join
zscheimer
zscheimer

Reputation: 11

Where Statement Within Inner-Join

I am trying to select info from our customers most recent purchase or abandonment and getting an error message at the Where statement.

SELECT Orders.EMAILADDRESS as EMAILADDRESS, [All Productions].REGIONID, [All Productions].EVENTTYPE, [All Productions].EVENTCATEGORY, [All Productions].EVENTNAME
FROM Orders INNER JOIN [All Productions]
WHERE Orders.ORDERDATE = (
     Select MAX(Orders.ORDERDATE)
     FROM Orders
     GROUP BY EMAILADDRESS
)
ON Orders. PRODUCTIONID = [All Productions].PRODUCTIONID

UNION
SELECT Carts.EMAILADDRESS as EMAILADDRESS, [All Productions].REGIONID, [All Productions].EVENTTYPE, [All Productions].EVENTCATEGORY, [All Productions].EVENTNAME
FROM Orders INNER JOIN [All Productions]
WHERE Carts.ABANDONDATE = (
     Select MAX(Carts.ABANDONDATE)
     FROM Carts
     GROUP BY EMAILADDRESS
)
ON Carts.ABANDONDID = [All Productions].PRODUCTIONID

Upvotes: 0

Views: 69

Answers (3)

Pரதீப்
Pரதீப்

Reputation: 93754

Join ON condition should come before the where clause

SELECT Orders.EMAILADDRESS AS EMAILADDRESS,
       [All Productions].REGIONID,
       [All Productions].EVENTTYPE,
       [All Productions].EVENTCATEGORY,
       [All Productions].EVENTNAME
FROM   Orders
       INNER JOIN [All Productions]
               ON Orders. PRODUCTIONID = [All Productions].PRODUCTIONID
       JOIN (SELECT Max(Orders.ORDERDATE) ORDERDATE,
                    EMAILADDRESS
             FROM   Orders
             GROUP  BY EMAILADDRESS) B
         ON Orders.ORDERDATE = B.ORDERDATE
            AND Orders.EMAILADDRESS = b.EMAILADDRESS
UNION
SELECT Carts.EMAILADDRESS AS EMAILADDRESS,
       [All Productions].REGIONID,
       [All Productions].EVENTTYPE,
       [All Productions].EVENTCATEGORY,
       [All Productions].EVENTNAME
FROM   Orders
       INNER JOIN [All Productions]
               ON Carts.ABANDONDID = [All Productions].PRODUCTIONID
       JOIN (SELECT Max(Carts.ABANDONDATE),EMAILADDRESS
             FROM   Carts
             GROUP  BY EMAILADDRESS) b
         ON b.ABANDONDATE = Carts.ABANDONDATE
            AND Orders.EMAILADDRESS = b.EMAILADDRESS

Upvotes: 2

Ron Smith
Ron Smith

Reputation: 3266

You can use the row_number function to get the latest date for each grouping:

SELECT * FROM(
    SELECT
        Orders.EMAILADDRESS as EMAILADDRESS,
        [All Productions].REGIONID,
        [All Productions].EVENTTYPE,
        [All Productions].EVENTCATEGORY,
        [All Productions].EVENTNAME,
        row_number() over(partition by Orders.EMAILADDRESS
                          order by Orders.ORDERDATE desc) as LATEST
    FROM Orders
        INNER JOIN [All Productions]
            ON Orders. PRODUCTIONID = [All Productions].PRODUCTIONID
    UNION
    SELECT
        Carts.EMAILADDRESS as EMAILADDRESS,
        [All Productions].REGIONID,
        [All Productions].EVENTTYPE,
        [All Productions].EVENTCATEGORY,
        [All Productions].EVENTNAME,
        row_number() over(partition by Carts.EMAILADDRESS
                          order by Carts.ORDERDATE desc) as LATEST
    FROM Carts
        INNER JOIN [All Productions]
            ON Carts.ABANDONDID = [All Productions].PRODUCTIONID)
WHERE LATEST = 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271023

The first problem is that where goes after on (on is part of the from clause; the where clause follows the from clause). So the structure of the query is not correct.

Even if you fixed the syntax, you have a second problem; the query would return an error, because the subquery would -- in all likelihood -- return more than one row. The first is what you seem to be trying to do with the first subquery:

SELECT o.EMAILADDRESS as EMAILADDRESS, ap.REGIONID,
       ap.EVENTTYPE, [All Productions].EVENTCATEGORY,
       ap.EVENTNAME
FROM Orders as o INNER JOIN
     [All Productions] as ap
     ON o.PRODUCTIONID = ap.PRODUCTIONID
WHERE o.ORDERDATE = (Select MAX(o2.ORDERDATE)
                     FROM Orders as o2
                     WHERE o2.EMAILADDRESS = o.EMAILADRESS
                    );

Upvotes: 1

Related Questions