Geek Answers Handbook

SQL query where where

I have this line in my sql query:

WHERE client = $id
    AND (isinvoiced = 0) OR (isinvoiced = 1 and isrecurring = 1)

which gets more results than I expect. However, if I write it like this:

WHERE client = $id
    AND (isinvoiced = 0) OR (isinvoiced = 1 and isrecurring = 1 and client = $id)

then it gets me the results I wanted, but is this the best way to write this? I just don't want to run any more problems with this code.

+5
source share
6 answers

You will need another set ()around the whole offer AND. This means that client = $idSHOULD be true, and any of the other conditions should also me true = isinvoiced = 0OR combination isinvoiced = 1 and isrecurring = 1.

 WHERE client = $id
    AND ((isinvoiced = 0) OR (isinvoiced = 1 and isrecurring = 1))
+8
source

Add a bracket around your sentence AND:

WHERE client = $id
    AND ((isinvoiced = 0) OR (isinvoiced = 1 and isrecurring = 1))
+2
source 
where client = $id
    and (
        isinvoiced = 0
        or (
            isinvoiced = 1
            and isrecurring = 1
            )
        )
+1

, :

WHERE client = $id AND ((isinvoiced = 0) OR (isinvoiced = 1 and isrecurring = 1))

blaquets, OR .

+1

As for SQL, you should strive to write down your search terms in conjunctive normal form ("sentence sir AND"). There are various rewriting rules to help with this.

In this case , the rewrite distribution law is useful , i.e.

( P AND Q ) OR R   <=>   ( P OR R ) AND ( Q OR R )

In your case:

( isinvoiced = 0 ) OR ( isinvoiced = 1 AND isrecurring = 1 )

can be rewritten as:

( isinvoiced = 0 OR isinvoiced = 1 ) AND ( isinvoiced = 0 OR isrecurring = 1 )

Therefore, the entire search term without bulky pars:

....
WHERE client = $id
      AND ( isinvoiced = 0 OR isinvoiced = 1 ) 
      AND ( isinvoiced = 0 OR isrecurring = 1 );
0
source

If you delete the original where clause

remove -> WHERE client = $id

and just

 WHERE (isinvoiced = 0) OR (isinvoiced = 1 and isrecurring = 1 and client = $id)

Do you get the results you want?

-1
source

More articles:


All Articles