-1

here is my current query and it access four tables. Everything else displays the correct data except for this part:

IF(tbl_shift.commission IS NULL,'0.00', (tbl_shift.commission*tbl_payment.subtotal)) AS comm,

this doesn't seem to access the data from the table.

Here is the full query:

SELECT tbl_payment.dateTime AS purchasedate, tbl_payment.invoiceNo AS invoice, 
        tbl_payment.subtotal AS total, tbl_payment.staffid AS employee, 
        tbl_users.fname AS firstname, tbl_users.lname AS lastname, 
        IF(tbl_shift.commission IS NULL,'0.00', (tbl_shift.commission*tbl_payment.subtotal)) AS comm, 
        (SELECT COUNT(1) AS qty FROM tbl_purchases 
        WHERE tbl_payment.invoiceNo=tbl_purchases.invoiceNo) AS qty 
FROM `tbl_payment` 
    LEFT JOIN tbl_shift ON tbl_payment.staffid = tbl_shift.employeeId 
        AND tbl_payment.dateTime BETWEEN tbl_shift.startShift 
        AND tbl_shift.endShift 
    INNER JOIN tbl_users ON tbl_payment.staffid = tbl_users.id 
WHERE tbl_payment.staffid = ".$staff." 
AND dateTime BETWEEN '".$from."' AND '".$to."' 
ORDER BY tbl_payment.dateTime DESC
Deepak Rai
  • 2,163
  • 3
  • 21
  • 36
Cei
  • 1
  • 1
  • See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query. And see about sql and the importance of prepared and bound queries – Strawberry Jan 29 '21 at 18:00
  • I suggest you select `tbl_shift.commission`, too, to see what it contains. You are outer joining `tbl_shift`. Maybe there is just no row matching. – Thorsten Kettner Jan 30 '21 at 00:19

1 Answers1

0

if this succeeds

IF(tbl_shift.commission IS NULL,'0.00', (tbl_shift.commission*tbl_payment.subtotal)) AS comm,

Wouldn't it mean that you would have both string values and numbers in the same column if both are true for any rows? Perhaps try this and see what happens:

IF(ISNULL(tbl_shift.commission)=1,0,(tbl_shift.commission*tbl_payment.subtotal)) AS comm,
Chuma
  • 714
  • 3
  • 7
  • It doesn't show the value in the tbl_shift.commission. – Cei Jan 30 '21 at 05:50
  • Right. My answer is a suggestion to fix it so it does show a value - essentially don’t mix a number and text in the same column. – Chuma Feb 01 '21 at 05:40