Custom query report of sales order

Frappe Framework Customization
1

hello,
I want to create new custom query report, In which If there are 10 items in sales order of different quantities and in sales invoice of same sales order there is variation in quantity of items or lets say 7 or 8 items in sales invoice, in this situation i want to create custom report in which it should have following columns.
I wrote query which is partially working for following column but I’m having problem in SO quantity and Invoiced quantity. I want the a items of quantity which are in sales order in SO quantity column. the quantity of item which is invoiced among items which are in sales order and also the item which is present in sales order but not in sales invoice their quantity should be zero in Invoiced
quantity column
columns are

*Sr. no
*customer name
*customer PO number
*sales order number
*Item code
*description
*sales Order quantity
*sales Invoiced quantity
*pending quantity (SO qty-SI qty)

please suggest the changes in following query
thanks in advance

SELECT 
    `tabSales Order`.`customer_name` AS 'Customer Name::150',
    `tabSales Order`.`po_no` AS 'Customer PO:130',
    `tabSales Order`.`name` AS 'Sales Order:Link/Sales Order:120',
    `tabSales Order Item`.item_code AS 'Item:Link/Item:120',
    `tabSales Order Item`.description AS 'Description::200',
    `tabSales Order Item`.qty AS 'Qty:Float:140',
    `tabSales Invoice Item`.qty AS 'Invoiced Qty:Float:140',
    (`tabSales Order Item`.qty - `tabSales Invoice Item`.qty) AS 'Pending Qty'
FROM
    `tabSales Order`
        JOIN
    `tabSales Order Item`
        JOIN
    `tabSales Invoice Item`
        LEFT JOIN
    `tabBin` ON (`tabBin`.item_code = `tabSales Order Item`.item_code
        AND `tabBin`.warehouse = `tabSales Order Item`.warehouse)
WHERE
    `tabSales Invoice Item`.sales_order = `tabSales Order`.name
        AND `tabSales Order Item`.`parent` = `tabSales Order`.`name`
        AND `tabSales Invoice Item`.item_code = `tabSales Order Item`.item_code
        AND `tabSales Order`.docstatus = 1
2

Don’t use simple join. Use INNER JOIN or LEFT JOIN as per requirement but always join on a unique column. Like Parent and Child table (i.e. Sales Order and Sales Order Item respectively) can be joined on the basis of

`tabSales Order`.name = `tabSales Order Item`.parent
3

thanks @root13F

I am new to Erpnext could you explain in above code?
and as per requirement, in invoiced quantity table there should be the quantity of item which is is SO as well as SI and it should place zero in column for the item which is not in SI but they are in SO.
usually in LEFT JOIN it will give all the quantity in SI.
lets say there are 2 items xyz and pqr in SO whose quantity is 2 and 2 respectively and in SI of same SO there is only 1 item whose quantity is 1so the result should be as follows

item =xyz,pqr
SO quantity=2,2
SI quantity =1,0
pending quantity=1,2