CODEWITHSUNDEEP
sqlt-sql
michox2
michox2

Reputation: 123

What is wrong with my joining derived table query?

Im pretty new to SQL and just started learning about derived tables. the thing is created two tables, one without null and one with null but i cant seem to understand why the join wont work. i get the errors "Incorrect syntax near the keyword 'OUTER'." and "Incorrect syntax near the keyword 'ON'." but i still don't understand...

here is my code: 

SELECT customer_id, order_id, order_status, shipped_date
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_status) AS rownum,
customer_id, order_id, order_status, shipped_date
FROM Sales.orders) AS T 
WHERE shipped_date IS NOT NULL

OUTER JOIN

SELECT customer_id, order_id, order_status, shipped_date
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_status) AS rownum,
customer_id, order_id, order_status, shipped_date
FROM Sales.orders) AS T2
WHERE shipped_date IS  NULL

ON T.customer_id = T2.customerid;

Upvotes: 1

Views: 90

Answers (1)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

try like below

    select a.*,b.*  from (SELECT customer_id, order_id, order_status, shipped_date
    FROM (
    SELECT ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_status) AS rownum,
    customer_id, order_id, order_status, shipped_date
    FROM Sales.orders) AS T 
    WHERE shipped_date IS NOT NULL
   ) a    
    left JOIN

    (SELECT customer_id, order_id, order_status, shipped_date
    FROM (
    SELECT ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_status) AS rownum,
    customer_id, order_id, order_status, shipped_date
    FROM Sales.orders) AS T2
    WHERE shipped_date IS  NULL
   ) b    
    ON a.customer_id = b.customerid;

BTW you can do it by using one query

SELECT customer_id, order_id, order_status, shipped_date
        FROM (
        SELECT ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_status) AS rownum,
        customer_id, order_id, order_status, shipped_date
        FROM Sales.orders) AS T2
        WHERE shipped_date IS  NULL or shipped_date is not null

Upvotes: 3

Related Questions