CODEWITHSUNDEEP
phpsql
user1269625
user1269625

Reputation: 3209

I am having trouble writing my SQL query

I have an sql query like this...

SELECT c.clientid, c.clientname, c.billingdate, 
       (SELECT ifnull(sum(total), 0) 
          FROM invoice i
         WHERE i.client = c.clientid AND i.isdeleted = 0) -
       (SELECT ifnull(sum(p.amount), 0) 
          FROM payment p
         INNER JOIN invoice i ON p.invoice = i.invoiceid
         WHERE i.client = c.clientid and i.isdeleted = 0) as balance, 
        CASE c.isactive+0 WHEN '1' THEN 'Stop'
                                   ELSE 'Start' END as Active
FROM client c
ORDER BY clientname

This works fine with no errors, but notice this part....

(SELECT ifnull(sum(total), 0) 
          FROM invoice i
         WHERE i.client = c.clientid AND i.isdeleted = 0) -(SELECT ifnull(sum(p.amount), 0) 
   FROM payment p
  INNER JOIN invoice i ON p.invoice = i.invoiceid
  WHERE i.client = c.clientid AND i.isdeleted = 0) as balance

I wrote a PHP script...

if($remaining < 0){
    $remaining = $row['total'];
}else{
    $remaining = $remaining + $row['total'];
}

What I am trying to do is incorporate what I wrote in my PHP to my SQL query, but I have never wrote an SQL query with if statements before (if they are allowed). How would incorporate my PHP script to my SQL query? Any suggestions?

Upvotes: 1

Views: 139

Answers (2)

hol
hol

Reputation: 8423

You can wrap your result and use it. Make sure total and remaining is part of your result.

SELECT tt.clientid, tt.clientname, tt.billingdate, tt.total, tt.remaining, tt.active 
  FROM (
            ... here goes all of your select but the the order by 
       ) tt
ORDER BY tt.clientid

With that construct you could do what you do in PHP

SELECT tt.clientid, tt.clientname, tt.billingdate, tt.total, tt.active,
       CASE WHEN tt.remaining < 0 then tt.total
                                  else tt.remaining - tt.total
            END as remaining
  FROM (
            ... here goes all of your select make sure you select a total and a remaining
       ) tt
ORDER BY tt.clientid

So what you do is to create a temporary view actually. Not sure about your data model but I think that would look like this

SELECT tt.clientid, tt.clientname, tt.billingdate, tt.total, tt.active,
       CASE WHEN tt.remaining < 0 then tt.total
                                  else tt.remaining - tt.total
            END as remaining
 FROM (
    SELECT c.clientid, c.clientname, c.billingdate, 
       (SELECT ifnull(sum(total), 0) 
          FROM invoice i
         WHERE i.client = c.clientid AND i.isdeleted = 0) as total,
       (SELECT ifnull(sum(total), 0) 
          FROM invoice i
         WHERE i.client = c.clientid AND i.isdeleted = 0) -
       (SELECT ifnull(sum(p.amount), 0) 
          FROM payment p
         INNER JOIN invoice i ON p.invoice = i.invoiceid
         WHERE i.client = c.clientid and i.isdeleted = 0) as remaining, 
        CASE c.isactive+0 WHEN '1' THEN 'Stop'
                                   ELSE 'Start' END as Active
      FROM client c
      ) TT
ORDER BY clientname

Upvotes: 1

GolezTrol
GolezTrol

Reputation: 116180

There is no if, but there is case which allows you to do pretty much the same. You already use a case in your query, so I think you know how it works. :)

Upvotes: 2

Related Questions