Replace duplicate records set null ot empty in the column mysql-CodePudding

this is mysql query i want to set duplicate value as null or empty

SELECT som.sale_invoice_id, CONCAT(cm.first_name,cm.last_name) AS customername, product_master.product_name from sale_invoice_master as som LEFT JOIN customer_master as cm ON som.customer_id = cm.customer_id LEFT JOIN product_sale_item_master as soi ON som.sale_invoice_id = soi.sale_invoice_id LEFT JOIN product_master ON soi.product_id = product_master.product_id LEFT JOIN vehicle_master ON soi.vehicle_id = vehicle_master.id

This is mycurrent result

sale_invoice_id     customername                product_name
1                   JummakhanDilawarkhan        Apollo TYRE 16.9-28 12PR KRISHAK GOLD -D
1                   JummakhanDilawarkhan        APOLLO  TUBE 7.50x16
2                   PareshKhanchandani          Apollo TL 155R13 AMAZER XL 8PR

i want this

  sale_invoice_id           customername                product_name
        1                   JummakhanDilawarkhan        Apollo TYRE 16.9-28 12PR KRISHAK GOLD -D
                                                        APOLLO  TUBE 7.50x16
        2                   PareshKhanchandani          Apollo TL 155R13

second duplicate row should be null or empty

CodePudding user response：

I don't have your data to work out with. So what I have done is taken your current result as my primary data and used a query to produce your output:

I have mentioned it all in db-fiddle

Sure enough, you can modify this to use Window Functions as you are using MariaDB 10.4. I have just mentioned a possible solution.

/*To create table*/
CREATE TABLE `sales_invoice_data` (
  `sales_invoice_id` int(11) DEFAULT NULL,
  `customername` varchar(50) DEFAULT NULL,
  `product_name` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*to insert data*/
insert into `sales_invoice_data` (`sales_invoice_id`, `customername`, `product_name`) values('1','JummakhanDilawarkhan','Apollo TYRE 16.9-28 12PR KRISHAK GOLD -D');
insert into `sales_invoice_data` (`sales_invoice_id`, `customername`, `product_name`) values('1','JummakhanDilawarkhan','APOLLO TUBE 7.50x16');
insert into `sales_invoice_data` (`sales_invoice_id`, `customername`, `product_name`) values('2','PareshKhanchandani','Apollo TL 155R13');

/*to retrieve your OP*/
SELECT 
sales_invoice_id,
customerName,
product_name
FROM 
(SELECT 
(CASE WHEN sales_invoice_id=@running_sales_id THEN '' ELSE sales_invoice_id END)  sales_invoice_id,
(CASE WHEN customername=@running_customer THEN '' ELSE customername END) customerName,
product_name,
(CASE WHEN @running_sales_id=0 THEN @running_sales_id:=sales_invoice_id ELSE @running_sales_id:=@running_sales_id END) ,
(CASE WHEN @running_customer='' THEN @running_customer:=customername ELSE @running_customer:=@running_customer END) ,
@running_sales_id:=a.sales_invoice_id,
@running_customer:=customername
FROM 
(SELECT 
  s.sales_invoice_id ,
  s.customername,
  s.product_name,
  @running_sales_id:=0,
  @running_customer:=''
FROM
  `sales_invoice_data`  s) a ) final
  ;

CodePudding user response：

One possible solution would involve using MySQL's LAG() function. Here are the docs for it:

https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_lag