CODEWITHSUNDEEP
mysqljoinleft-join
bumbumpaw
bumbumpaw

Reputation: 2530

Set columns to null or empty in JOIN query

Order Table

ORDER_ID|ORDER_DT
1       |12-12-2016

Product Table

PRODUCT ID | ORDER ID
1          | 1
2          | 1

using SELECT * FROM order o LEFT JOIN product p ON o.order_id = p.order_id;

results

 PRODUCT ID | ORDER ID|ORDER_DT
 1          | 1       |12-12-2016
 2          | 1       |12-12-2016
 3          | 1       |12-12-2016

but I need to have this kind of results,noticed that I have still the same nos. of rows but duplicates in order table columns was emptied.

PRODUCT ID | ORDER ID|ORDER_DT
     1     | 1       |12-12-2016
     2     |         |
     3     |         |

Upvotes: 0

Views: 26

Answers (1)

Darshan Mehta
Darshan Mehta

Reputation: 30809

You can do it with temoorary variables, e.g.:

SELECT product_id, orders.order_id, order_dt, IF(@previous = orders.order_id, 0, orders.order_id) AS oid, @previous:= orders.order_id
FROM orders
LEFT JOIN products
ON orders.order_id = products.order_id,
(SELECT @previous := -1) a;

And wrap it into another SELECT query to get the required columns, e.g.:

SELECT r.product_id, IF(r.oid = 0, '', r.oid) AS order_id, r.order_dt 
FROM (
    SELECT product_id, orders.order_id, order_dt, IF(@previous = orders.order_id, 0, orders.order_id) AS oid, @previous:= orders.order_id
    FROM orders
    LEFT JOIN products
    ON orders.order_id = products.order_id,
    (SELECT @previous := -1) a
    ) r;

Here's the SQL Fiddle.

Upvotes: 1

Related Questions