CREATE OR REPLACE ALGORITHM = MERGE VIEW `sellbillview` AS
SELECT sellbill.sellbillid,sellbillserial,sellbillaftertotalbill,sellbilldate,sellQuantity,sellbilltotalpayed,sellbillfinalbill,sellbill.conditions,sellbill.dailyentryid,sellbillclientid,sellerid,sellbill.userid,sellbillstoreid,sellbillsaveid, client.clientname, user.employeename , user2.employeename  as  useremployeename,storeName,savename, sellbilldetail.pricetype
			 FROM sellbill
             join sellbilldetail on sellbilldetail.sellbillid = sellbill.sellbillid
                         LEFT JOIN client ON sellbill.sellbillclientid = client.clientid
                         JOIN store ON sellbill.sellbillstoreid = store.storeId
                         JOIN save ON sellbill.sellbillsaveid = save.saveid
                         LEFT JOIN user ON sellbill.sellerid = user.userid
			 LEFT JOIN user as user2 ON sellbill.userid = user2.userid
                         group by sellbill.sellbillid;--LEFT JOIN sellbill as editedBill ON sellbill.sellbillid = editedBill.deletedsellid   ---,editedBill.sellbillid as newSellbillidAfterEdit

CREATE OR REPLACE ALGORITHM = MERGE VIEW `sellbillandruternview`	AS
SELECT sellbillandrutern.sellbillid,sellbillaftertotalbill,sellbilldate,sellQuantity,returnsellQuantity,sellbilltotalpayed,sellbillfinalbill,sellbillandrutern.conditions,sellbillandrutern.dailyentryid,sellbillclientid,sellerid,sellbillandrutern.userid,sellbillstoreid,sellbillsaveid, client.clientname, user.employeename , user2.employeename  as  useremployeename,storeName,savename, sellandruternbilldetail.pricetype
			 FROM sellbillandrutern
             join sellandruternbilldetail on sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
			 LEFT JOIN client ON sellbillandrutern.sellbillclientid = client.clientid
			 JOIN store ON sellbillandrutern.sellbillstoreid = store.storeId
                         JOIN save ON sellbillandrutern.sellbillsaveid = save.saveid
                         LEFT JOIN user ON sellbillandrutern.sellerid = user.userid
			 LEFT JOIN user as user2  ON sellbillandrutern.userid = user2.userid
						 group by sellbillandrutern.sellbillid;--LEFT JOIN sellbillandrutern as editedBill ON sellbillandrutern.sellbillid = editedBill.deletedsellid   ---,editedBill.sellbillid as newSellbillidAfterEdit

CREATE OR REPLACE ALGORITHM = MERGE VIEW `returnsellbillview` AS
SELECT returnsellbill.returnsellbillid,returnsellbillserial,returnsellbillaftertotalbill,returnsellbilldate,returnsellQuantity,returnsellbilltotalpayed,returnsellbillfinalbill,returnsellbill.conditions,returnsellbill.dailyentryid,returnsellbillclientid,sellerid,returnsellbill.userid,returnsellbillstoreid,returnsellbillsaveid, client.clientname, user.employeename , user2.employeename  as  useremployeename,storeName,savename, returnsellbilldetail.pricetype
			 FROM returnsellbill
						join returnsellbilldetail on returnsellbilldetail.returnsellbillid = returnsellbill.returnsellbillid
                         LEFT JOIN client ON returnsellbill.returnsellbillclientid = client.clientid
                         JOIN store ON returnsellbill.returnsellbillstoreid = store.storeId
                         JOIN save ON returnsellbill.returnsellbillsaveid = save.saveid
                         LEFT JOIN user ON returnsellbill.sellerid = user.userid
			 LEFT JOIN user as user2 ON returnsellbill.userid = user2.userid
							group by returnsellbill.returnsellbillid;--LEFT JOIN returnsellbill as editedBill ON returnsellbill.returnsellbillid = editedBill.deletedsellid   ---,editedBill.returnsellbillid as newSellbillidAfterEdit


--for clientdebt.php
CREATE OR REPLACE ALGORITHM = MERGE VIEW `clientLastDeals` AS
select client.clientid,clientname,clientdebt,clientphone,clientmobile,clientarea.name as areaName,qclientdebtchange.lastClientDebtChangeDate
	   ,qsellbill.lastSellBillDate,qreturnsellbill.lastReturnSellBillDate,qsellbillandrutern.lastSellBillAndRuternDate
from client
left join clientarea on clientarea.id=client.clientareaid
left join (
	SELECT clientid,max(clientdebtchangedate) lastClientDebtChangeDate
	from clientdebtchange
	where clientdebtchange.tablename = "clientPayedDeptController.php"
	and del = 0
	) as qclientdebtchange on qclientdebtchange.clientid = client.clientid
left join (
    SELECT sellbillclientid,max(sellbilldate) as lastSellBillDate
    FROM sellbill
	where conditions = 0
    group by sellbillclientid
    ) as qsellbill on qsellbill.sellbillclientid = client.clientid
left join (
    SELECT sellbillclientid,max(sellbilldate) as lastSellBillAndRuternDate
    FROM sellbillandrutern
	where conditions = 0
    group by sellbillclientid
    ) as qsellbillandrutern on qsellbillandrutern.sellbillclientid = client.clientid
left join (
    SELECT returnsellbillclientid,max(returnsellbilldate) as lastReturnSellBillDate
    FROM returnsellbill
	where conditions = 0
    group by returnsellbillclientid
    ) as qreturnsellbill on qreturnsellbill.returnsellbillclientid = client.clientid
where client.conditions = 0;

-- ------------------------------------------------------
-- the simple views
CREATE OR REPLACE ALGORITHM = MERGE VIEW `sellbillview` AS
    SELECT sellbill.sellbillid,sellbill.sellbillserial,sellbill.sellbillaftertotalbill,sellbill.sellbilldate,sellbill.sellbilltotalpayed,sellbill.sellbillfinalbill,sellbill.conditions,sellbill.sellbillclientid,sellbill.sellerid,sellbill.userid,sellbill.sellbillstoreid,sellbill.sellbillsaveid, client.clientname,editedBill.sellbillid as newSellbillidAfterEdit,sellbill.dailyentryid
    FROM sellbill
    LEFT JOIN client ON sellbill.sellbillclientid = client.clientid
    LEFT JOIN sellbill as editedBill ON sellbill.sellbillid = editedBill.deletedsellid;

CREATE OR REPLACE ALGORITHM = MERGE VIEW `sellbillandruternview`	AS
SELECT sellbillandrutern.sellbillid,sellbillandrutern.sellbillaftertotalbill,sellbillandrutern.sellbilldate,sellbillandrutern.sellbilltotalpayed,sellbillandrutern.sellbillfinalbill,sellbillandrutern.conditions,sellbillandrutern.sellbillclientid,sellbillandrutern.sellerid,sellbillandrutern.userid,sellbillandrutern.sellbillstoreid,sellbillandrutern.sellbillsaveid, client.clientname,editedBill.sellbillid as newSellbillidAfterEdit,sellbillandrutern.dailyentryid
			 FROM sellbillandrutern
			 LEFT JOIN client ON sellbillandrutern.sellbillclientid = client.clientid
                         LEFT JOIN sellbillandrutern as editedBill ON sellbillandrutern.sellbillid = editedBill.deletedsellid;

CREATE OR REPLACE ALGORITHM = MERGE VIEW `returnsellbillview` AS
SELECT returnsellbill.returnsellbillid,returnsellbill.returnsellbillserial,returnsellbill.returnsellbillaftertotalbill,returnsellbill.returnsellbilldate,returnsellbill.returnsellbilltotalpayed,returnsellbill.returnsellbillfinalbill,returnsellbill.conditions,returnsellbill.returnsellbillclientid,returnsellbill.sellerid,returnsellbill.userid,returnsellbill.returnsellbillstoreid,returnsellbill.returnsellbillsaveid, client.clientname,editedBill.returnsellbillid as newSellbillidAfterEdit,returnsellbill.dailyentryid
			 FROM returnsellbill
             LEFT JOIN client ON returnsellbill.returnsellbillclientid = client.clientid
             LEFT JOIN returnsellbill as editedBill ON returnsellbill.returnsellbillid = editedBill.deletedsellid;


-- -----------------------------------------------------------------------------------
-- remove first char of parcode if its length > 5
UPDATE product SET parcode = SUBSTR(parcode, 2) WHERE CHAR_LENGTH (`parcode`) > 5 and parcode LIKE '0%';

-- ------------------------------------------------------------------------------------------------
-- get client with error in debt to review
-- 1-make sure tables do exist
-- note drop "clientdebtchangetemp" means lose clientdebtcalc
drop table if exists clienttemp;
-- note drop "clientdebtchangetemp" means rework do it only if necessary
drop table if exists clientdebtchangetemp;
CREATE TABLE IF NOT EXISTS `clienttemp` (
  `clientid` int(11) NOT NULL AUTO_INCREMENT,
  `clientdebtcalc` float NOT NULL,
  PRIMARY KEY (`clientid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE IF NOT EXISTS `clientdebtchangetemp` (
  `clientdebtchangeid` int(11) NOT NULL,
  `clientid` int(11) NOT NULL,
  `clientdebtchangebefore` float NOT NULL,
  `clientdebtchangeamount` float NOT NULL,
  `clientdebtchangetype` int(11) NOT NULL,
  `clientdebtchangeafter` float NOT NULL,
  `tablename` varchar(256) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- LOCK TABLES client WRITE,clienttemp WRITE,clientdebtchange WRITE,clientdebtchangetemp WRITE;

-- 2-insert data into it
INSERT INTO clienttemp
SELECT clientid,0 FROM client where clientid !=1;
-- note insert only new rows
INSERT INTO clientdebtchangetemp
SELECT clientdebtchangeid,clientid,clientdebtchangebefore,clientdebtchangeamount,clientdebtchangetype,clientdebtchangeafter,tablename
FROM clientdebtchange
where del = 0 and tablename != "clientDeficitController.php" and clientid !=1 and clientdebtchangeid > 0000; -- 0000 is last clientdebtchangeid in temp table before using the last " INSERT INTO clientdebtchangetemp " statment
-- use this statment only if you drop table clientdebtchangetemp at begining as we need id column
ALTER TABLE clientdebtchangetemp ADD id INT PRIMARY KEY AUTO_INCREMENT FIRST;

-- 3-reverse the clientdebtchangetype for new rows only as clientPayedDeptController.php type is entered in reverse way
UPDATE clientdebtchangetemp
SET clientdebtchangetype = CASE clientdebtchangetype WHEN 0 THEN 1 WHEN 1 THEN 0 ELSE clientdebtchangetype END
where tablename="clientPayedDeptController.php" and id > 0000; -- 0000 is last clientdebtchangeid in temp table before using the last " INSERT INTO clientdebtchangetemp " statment

-- UNLOCK TABLES;


-- 4-create procedure goes row by row to re calc debtbefore and after for each row in clientdebtchangetemp
DROP PROCEDURE IF EXISTS ROWPERROW;
DELIMITER ;;

CREATE PROCEDURE ROWPERROW(IN i INT)
BEGIN
DECLARE n INT DEFAULT 0; -- n for total rows to loop through
-- DECLARE i INT DEFAULT 1; -- i for current row id
DECLARE debtbefore INT DEFAULT 0; -- save debt before from previos row in it
DECLARE clientdebtchangetypeinrow INT DEFAULT 0; -- save current type
DECLARE clientidinrow INT DEFAULT 0; -- save clientid
SELECT COUNT(*) FROM clientdebtchangetemp INTO n; -- no of rows of the table needed in while
WHILE i <= n DO
	-- save client id of row and reset debtbefore
	SELECT clientid FROM clientdebtchangetemp where id=i INTO clientidinrow;
	SET debtbefore=0;
	-- get debtbefore and save it in the row
	SELECT clientdebtchangeafter FROM clientdebtchangetemp where id<i and clientid = clientidinrow order by id desc limit 1  INTO debtbefore;
	update clientdebtchangetemp set clientdebtchangebefore  = debtbefore where id=i;

	-- get clientdebtchangetype to calc clientdebtchangeafter with + for type=0 and - for type=1
	SELECT clientdebtchangetype FROM clientdebtchangetemp where id=i INTO clientdebtchangetypeinrow;
	IF clientdebtchangetypeinrow = 1 THEN
	   update clientdebtchangetemp set clientdebtchangeafter  = clientdebtchangebefore - clientdebtchangeamount where id = i;
	ELSE
	   update clientdebtchangetemp set clientdebtchangeafter  = clientdebtchangebefore + clientdebtchangeamount where id = i;
	END IF;
	-- save new clc debt in clienttemp
	update clienttemp set clientdebtcalc  = (select clientdebtchangeafter from clientdebtchangetemp where id=i) where clientid=clientidinrow;

	-- move to next i
	SET i = i + 1;
END WHILE;
End;
;;

DELIMITER ;



-- call stored procedure by name and pass no of row to start recalculating debtbefore and after
CALL ROWPERROW(1);
-- this drops the stored procedure
DROP PROCEDURE IF EXISTS ROWPERROW;
-- ------------------------------------------------------------------------------------------------
-- update with join example
UPDATE T1,T2
INNER JOIN T2 ON T1.C1 = T2.C1
SET T1.C2 = T2.C2,
      T2.C3 = expr
WHERE condition;
-- like
UPDATE productserial
JOIN buybill ON productserial.billid = buybill.buybillid
SET productserial.storeid = buybill.buybillstoreid
---------------------------------------------------------------------------------------------------
--
ALTER TABLE table ADD UNIQUE KEY column1 (column1);
INSERT IGNORE INTO table(column1, column2, column3) VALUES(@value1, @value2, @value3);
-- or if you want to automatically update the other fields, use ON DUPLICATE KEY UPDATE:
INSERT INTO table(column1, column2, column3) VALUES(@value1, @value2, @value3)
ON DUPLICATE KEY UPDATE column2 = @value2,  column3 = @value3;

-- letting the database handle it automatically using unique key has several advantages:
--      *better performance and scalability through usage of indexes as opposed to full table scans
--      **failsafe - you can not add two rows with same unique identifier accidentally with a wrong query
--      ***better readability of your queries
-- NOTE: a unique key may also span multiple rows. stupid example: combination of "ip" and "port" may be combined as the unique identifier "connection".


--------------------------------------------------------------------------------
-- updateCollectiveProductsPrices as sum of ingridients cost
UPDATE product
JOIN (
       select productingredients.productId
              ,sum(productnumber * quantity * productBuyPrice) as productBuyPrice
              ,sum(productnumber * quantity * lastbuyprice) as lastbuyprice
              ,sum(productnumber * quantity * lastbuyprice_withDiscount) as lastbuyprice_withDiscount
              ,sum(productnumber * quantity * meanbuyprice) as meanbuyprice
              ,sum(productnumber * quantity * meanbuyprice_withDiscount) as meanbuyprice_withDiscount
              ,sum(productnumber * quantity * overAllAveragePrice) as overAllAveragePrice
      from product
      join productingredients on product.productId = productingredients.ingridientId
      join productunit on productunit.productunitid = productingredients.unitId
      group by productingredients.productId
    ) as ingridientPrice ON ingridientPrice.productId =  product.productId
SET product.productBuyPrice = ingridientPrice.productBuyPrice,
	product.lastbuyprice = ingridientPrice.lastbuyprice,
    product.lastbuyprice_withDiscount = ingridientPrice.lastbuyprice_withDiscount,
    product.meanbuyprice = ingridientPrice.meanbuyprice,
    product.meanbuyprice_withDiscount = ingridientPrice.meanbuyprice_withDiscount,
    product.overAllAveragePrice = ingridientPrice.overAllAveragePrice;
--------------------------------------------------------------------------------
-- update using another table from another db on same host
-- also diffent encoding "collation" of text
UPDATE royalerp.user AS u
JOIN royal.awusers AS a on (a.name=u.employeename  COLLATE utf8_unicode_ci and u.obygyDoctorId < 1)
SET u.obygyDoctorId = a.user_id

-- for royal joining with erp
UPDATE royalerp.client AS c
JOIN royal.patients AS p on p.id=c.obygyPatientId
SET c.clientname = p.wifename,c.husbandNameObgy = p.husdandname
where p.wifename != "" and  p.wifename is not null;


UPDATE royalerp.client AS c
JOIN royal.patients AS p on p.id=c.obygyPatientId
SET c.clientname = p.husdandname,c.husbandNameObgy = ''
where p.wifename = "" or  p.wifename is null;

UPDATE accountstree
join client on client.`treeId` = accountstree.id
SET name = clientname, customName = clientname, customNameEn = clientname, nameEn = clientname;

--------------------------------------------------------------------------------
-- Get records with max value for each group of grouped SQL results
-- group by but get biggest id in the group by result
SELECT o.*
FROM `Persons` o                    # 'o' from 'oldest person in group'
  LEFT JOIN `Persons` b             # 'b' from 'bigger age'
      ON o.Group = b.Group AND o.Age < b.Age
WHERE b.Age is NULL                 # bigger age not found



*****************
How it works:
It matches each row from o with all the rows from b having the same value in column Group and a bigger value in column Age. Any row from o not having the maximum value of its group in column Age will match one or more rows from b.

The LEFT JOIN makes it match the oldest person in group (including the persons that are alone in their group) with a row full of NULLs from b ('no biggest age in the group').
Using INNER JOIN makes these rows not matching and they are ignored.

The WHERE clause keeps only the rows having NULLs in the fields extracted from b. They are the oldest persons from each group.
******************



ex:-

SELECT o.*
FROM `buybilldetail` o                    
  LEFT JOIN `buybilldetail` b             
      ON o.buybilldetailproductid = b.buybilldetailproductid AND o.buybilldetailid < b.buybilldetailid
WHERE b.buybilldetailid is NULL
--------------------------------------------------------------------------------                
-- create dummy or random data for testing with mysql

-- table structure
CREATE TABLE my_table (
  project VARCHAR(255),
  task_name VARCHAR(255),
  start_date DATE,
  end_date DATE,
  status VARCHAR(50),
  assigned_to VARCHAR(255),
  cost DECIMAL(10, 2),
  budget DECIMAL(10, 2)
);

-- notmal insert into select statment ,  but it will insert one row ,,, so to insert more we use     insert into select from  "table or just rows with numbers have count we need"
INSERT INTO my_table (project, task_name, start_date, end_date, status, assigned_to, cost, budget)
SELECT 
  CONCAT('Project ', LPAD(FLOOR(1 + RAND() * 5), 2, '0')) AS project,
  CONCAT('Task ', LPAD(FLOOR(1 + RAND() * 20), 2, '0')) AS task_name,
  DATE_ADD(CURDATE(), INTERVAL FLOOR(1 + RAND() * 90) DAY) AS start_date,
  DATE_ADD(CURDATE(), INTERVAL FLOOR(91 + RAND() * 180) DAY) AS end_date,
  CASE FLOOR(1 + RAND() * 5)
    WHEN 1 THEN 'New'
    WHEN 2 THEN 'In Progress'
    WHEN 3 THEN 'On Hold'
    WHEN 4 THEN 'Completed'
    ELSE 'Cancelled'
  END AS status,
  CONCAT('User ', LPAD(FLOOR(1 + RAND() * 50), 2, '0')) AS assigned_to,
  FLOOR(1 + RAND() * 10000) AS cost,
  FLOOR(1000 + RAND() * 9000) AS budget
FROM  ( 


SELECT @i := @i+1 AS num
FROM (
  SELECT 1 AS x UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
  SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 AS x UNION ALL 
  SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
) AS t
CROSS JOIN (
  SELECT 1 AS x UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
  SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 AS x UNION ALL 
  SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
) AS s
CROSS JOIN (
  SELECT 1 AS x UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
  SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 AS x UNION ALL 
  SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
) AS u
CROSS JOIN (SELECT @i := 999) AS var


) as temp -- this is temp table with 1000 rows created on runtime  to insert 1000 row of dummy data @ my_table
-- how it is 1000 ,
-- Using CROSS JOIN on a 10 by 10 by 10 table combination we get a 1000 row in-line table. Using this as a basis we can calculate the required sequence of @i values.
-- also note := 999   means graeter than 999 ,,,, use 0 instead to test diffrence in numbers @ temp table


-- update table with subquery from it is not allowed 
-- ex of problem ------
UPDATE client
SET client.clientname = clientid
where clientid in (

    SELECT MIN(`clientdebtchangeid`) AS min_id, clientid,clientdebtchangedate
    FROM  clientdebtchange
    GROUP BY clientid
    having date(`clientdebtchangedate`) < '2023-09-15') as temp
   join client on (client.clientid = temp.clientid and client.clientdebt =0 and client.clientid > 1


);
-- #1093 - You can't specify target table 'client' for update in FROM clause


-- solution using CREATE TEMPORARY -----
CREATE TEMPORARY TABLE temp_table
select client.clientid,client.obygyPatientId
from 
(SELECT MIN(`clientdebtchangeid`) AS min_id, clientid,clientdebtchangedate
  FROM  clientdebtchange
  GROUP BY clientid
  having date(`clientdebtchangedate`) < '2023-09-15') as temp
 join client on (client.clientid = temp.clientid and client.clientdebt =0 and client.clientid > 1)  ;


UPDATE client
SET client.clientname = clientid
where clientid in (
  SELECT clientid
  FROM temp_table
);
-- -----------------------------------------------------------------------------