How optimize this query?

[newtomysql]

Dear All,
I have a query labelled as Query1 and it works perfectly fine and fast even to a remote pc. Unfortunately I have another query to run which now I run based on each value of the receiptID which I have labelled as query2. The problem I cant join it into my query1 is that for each receiptIID I have may have a match of more than one line in tblPaymentDetails so if I do a join I will only get of the paymentdetails row. The problem here is that when I run the Query2 based on Query1 results I am running two queries and this causes my entire system to slow down badly. Is there an thing I can improve here? Thanks.


Query1 = Select Cast(Concat(tblOutlet.outletCode,tblReceipt.receiptID) As Char) as receiptID,
tblOutlet.outletCode,
tblEmployee.employeeUserName,
tblProduct.productCode,
tblReceiptDetails.productSIQ,
tblReceiptDetails.sellingPrice,
tblReceipt.receiptID,
tblReceipt.outletID
From tblReceipt
Inner Join tblReceiptDetails On
tblReceipt.receiptID = tblReceiptDetails.receiptID
And tblReceipt.outletID = tblReceiptDetails.outletID
Inner Join tblEmployee On
tblReceipt.employeeID = tblEmployee.employeeID
Inner Join tblProduct On
tblReceiptDetails.productID = tblProduct.productID
Inner Join tblOutlet On
tblReceipt.outletID = tblOutlet.outletID
Where tblReceipt.receiptDate Between
'2009-01-01' And '2009-01-18' Order By tblReceipt.receiptDate

Query2=Select tblPaymentDetails.paymentTypeID,
tblPaymentDetails.creditCardTypeID,
tblPaymentDetails.amount
From tblPaymentDetails
Where tblPaymentDetails.receiptID=+ Convert.ToInt32(myReader1.GetValue(8).ToString()) +
And tblPaymentDetails.outletID=" + Convert.ToInt32(myReader1.GetValue(9).ToString());

 

[r937]

for each receiptIID I have may have a match of more than one line in tblPaymentDetails so if I do a join I will only get of the paymentdetails row

i understand that it is a one-to-many relationship, i just don't understand what's wrong with adding the join into query 1

do you perhaps want the sum of all payments for each receipt instead?



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[newtomysql]

Dear r937,
Yes you are right the relationship is one receiptID to many paymentDetails. Yes I want the sum of all payemtns types. Say for example receiptID=1 and we have 5 types of payments. So what I want in summary will be for each type of payment and their sum relating to receiptID=1. In this case it will be paymentType1=30,paymentType2=130,paymentType3=80 and paymentType4=90. Thanks.
receiptID=1,paymentTypeID=1,amout=30;
receiptID=1,paymentTypeID=2,creditCardTypeID=1,amout=60;
receiptID=1,paymentTypeID=2,creditCardTypeID=2,amout=70;
receiptID=1,paymentTypeID=3,amout=80;
receiptID=1,paymentTypeID=5,amout=90;

 

[r937]

SELECT CAST(CONCAT(tblOutlet.outletCode,tblReceipt.receiptID) AS CHAR) AS receiptID
      , tblOutlet.outletCode
      , tblEmployee.employeeUserName
      , tblProduct.productCode
      , tblReceiptDetails.productSIQ
      , tblReceiptDetails.sellingPrice
      , [red]pay.sum_payments[/red]
      , tblReceipt.receiptID
      , tblReceipt.outletID
  FROM tblReceipt
INNER 
  JOIN tblReceiptDetails 
    ON tblReceiptDetails.receiptID = tblReceipt.receiptID
   AND tblReceiptDetails.outletID = tblReceipt.outletID
[red]INNER
  JOIN ( SELECT receiptID
              , outletID
              , paymentTypeID
              , SUM(amount) AS sum_payments
           FROM tblPaymentDetails
         GROUP
             BY receiptID
              , outletID ) AS pay
    ON pay.receiptID = tblReceipt.receiptID
   AND pay.outletID = tblReceipt.outletID[/red]
INNER 
  JOIN tblEmployee 
    ON tblEmployee.employeeID = tblReceipt.employeeID
INNER 
  JOIN tblProduct 
    ON tblProduct.productID = tblReceiptDetails.productID
INNER 
  JOIN tblOutlet 
    ON tblOutlet.outletID = tblReceipt.outletID
 WHERE tblReceipt.receiptDate 
       BETWEEN '2009-01-01' AND '2009-01-18' 
ORDER 
    BY tblReceipt.receiptDate

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[newtomysql]

Dear R937,
The problem here is that I would like to receive not the total payment details but the sum of each type of payments. So for example based on the example below I want to see the sum individual for paymentType1=30,paymentType2=130,paymentType3=80 and paymentType4=90. I hope I am not confusing you here right. Thanks.

 

[r937]

no, i am not confused



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[newtomysql]

Dear R937,
Thank you for understanding my problem.Hope you can see some light to help me in this problem. Thanks.

 

[r937]

i thought i did -- did you try the query i suggested?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[newtomysql]

Dear R937,
Yes I did with your suggestion but it gives me the sum of total payment. I want to get the total sum of each different type of payment. Thanks.

 

[r937]

ah, i see the problem

add [red]pay.paymentTypeID[/red] to the SELECT clause


r937.com | rudy.ca
Buy my new book Simply SQL from Amazon