this is the query
WITH journal as (select journal_entry.SaleID as [invoice No.], description, journal_entry.debit as [Debit], journal_entry.credit as [Paided], journal_entry.[CustomerID] as [journal Customer id] from journal_entry ), SALES AS ( SELECT sale.saleid, invoice.InvoiceID, sale.totalpaid, item.itemname AS item, stock.saleprice AS SalePice, invoice.qty, invoice.StockID as [invoice Stock id], sale.discount,invoice.saleprice AS [invoice saleprice], cetegory.catname AS [Cateogory], cetegory.subcat AS [Sub Catgry], item.model_number AS [Part No.], invoice.Description as [invoice description], sale.date, stock.size, sale.customerid AS [cust sale id ], customer.customercontact, customer.customeraddress, customer.customerid, customer.CustomerName FROM invoice JOIN item ON invoice.itemid = item.itemid JOIN sale ON invoice.saleid = sale.saleid JOIN stock ON invoice.StockID = Stock.StockID JOIN cetegory ON item.catid = cetegory.catid join customer on sale.CustomerID = customer.CustomerID where sale.SaleID = 2 ) SELECT distinct * FROM journal right join SALES on journal.[invoice No.] = SALES.SaleID order by SALES.SaleID desc;
Now here i have some problem 1) i return only 'Asia Dryer 600' and it show for the item too. i want to skip this for other which has not return 2) The paided, debit and discount amount has been paid according to one saleid and it show me every same sale id i want when the sale id is same then only first rows of having the same sale id, these (paided, debit and discount) amount show and for else it should be zero or null.,
