Jump to content

Sql query problem


gmc1103
Go to solution Solved by Psycho,

Recommended Posts

Hi

 

I'm looking to build an effective query but i'm having a problem.

Barand suggest me this query

SELECT COUNT(*) AS total 
FROM `ebspma_paad_ebspma`.`req_material_reserva` 
WHERE `idequipamento` = $equip 
    AND `idsala` = $sala
    AND `idtempoInicio` < $fim 
    AND `idTempoFim` > $inicio
    AND `data` = $data

But i'm having this problem

 

 

query.png

 

But with this query

SELECT COUNT( * ) AS total FROM  `ebspma_paad_ebspma`.`req_material_reserva`  WHERE  `idequipamento` IS NULL OR  `idequipamento` =12
AND  `idsala` =13 AND  `idtempoInicio` <2 AND  `idTempoFim` >2 AND  `data` =  "2015-06-12"

I still have one result, but i don't have any "idtempoInicio" with 2 and any "idTempoFim" with 2

 

So the this query gives wrong result

 

Any help

Link to comment
Share on other sites

I think you misunderstand how a COUNT() query will work. I am assuming you are counting the number of records in the result set - which will always be 1. You will ALWAYS get a result - that result will contain the number of the COUNT() calculation. Using the example data and query you have above you should get a result with one record. That one record will contain a value of 0 - because there were no matching records. You need to extract the value from that result to get the number you are after.

Link to comment
Share on other sites

Hi Psycho

 

Thanks for your answer

Yes, i know i must always a value (0,1,2,3,etc) but in this case it should be 0 and i get 1

 

Comando SQL: SELECT COUNT(*) AS total FROM `ebspma_paad_ebspma`.`req_material_reserva` WHERE `idequipamento` is null or `idequipamento` = 12 AND `idsala` = 13 AND `idtempoInicio` < 2 AND `idTempoFim` > 2 AND `data` = "2015-06-12";
Registos: 1

dot.gif
total   1
 

 

And in my database i don't have  i don't have any "idtempoInicio" with 2 and any "idTempoFim" with 2

Link to comment
Share on other sites

  • Solution

Ah,

 

The problem is the OR condition. Operators have a process in how they are interpreted. For AND/OR operators that are interpreted from left to right. For an OR operator, if the condition(s) to the left are True or if the condition(s) to the right are True - then the result is True. Your conditions are being interpreted as this

 

 

If `idequipamento` IS NULL
 
OR
 
`idequipamento` =12
 AND  `idsala` =13
 AND  `idtempoInicio` <2
 AND  `idTempoFim` >2
 AND  `data` =  "2015-06-12"

 

The first record matches the condition because idequipamento is NULL.

 

You need to use parenthesis to group conditions to be interpreted how you wish them to be interpreted

 

 

SELECT COUNT( * ) AS total
 
FROM  `ebspma_paad_ebspma`.`req_material_reserva` 
 
WHERE  (`idequipamento` IS NULL OR  `idequipamento` =12)
  AND  `idsala` = 13
  AND  `idtempoInicio` <2
  AND  `idTempoFim` >2
  AND  `data` = "2015-06-12"
Link to comment
Share on other sites

 

I didn't realise that i had to use of parenthesis 

 

It's a lot like order of operations in math. If I want to add 2 and 3 and then multiple the result by 4, this will not work:

 

2 + 3 * 4

 

In math, multiplication and division are performed first. So the result of that would be

 

2 + 3 * 4  =  2 + 12  =  14

 

To get the intended result, you would have to enclose the addition in parenthesis

 

(2 + 3) * 4  =  5 * 4  =  20

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.