Specific Data Query

Question

Link for previous thread- LINKED Oracle - runtime and show data based on inserted year ( not duplicate )

Summary

Based on the link above, I have continued to code more specfic result that I want, which i have met some problem during the code which is using "WHERE" statement for showing specfic data after using subsitution variable(&)

Based on the working code that provided by Mr.Barboros, I wanted to add some query to show ONLY some location , example,

I have query 2016 , which should only show 2 result

FACILITYNAME COUNT_TIMES    MONTHS
Science Lab     1             4
Science Lab     1             5

But it always show extra Location which is Biology even in 2016 there is no Biology inserted to the DB. - If i added more location with "OR" , it will still continue show the result , below is the code for my query.

SELECT f.FACILITYNAME,COUNT(*) AS count_times, EXTRACT (MONTH FROM b.Timebooked ) AS MONTHS
FROM BookingTable b
JOIN FacilityTable f ON b.FACILITYNO = f.FACILITYNO
WHERE TO_CHAR(b.Timebooked , 'YYYY') = 2018
AND f.FACILITYNAME = 'Toilet' OR f.FACILITYNAME = 'Science Lab' OR  f.FACILITYNAME = 'Biology Lab'
GROUP BY (EXTRACT (MONTH FROM b.Timebooked )),f.FACILITYNAME
ORDER BY (EXTRACT (MONTH FROM b.Timebooked ));

http://sqlfiddle.com/#!4/13a6a/9

The result that I want is, when i entered any year , only show Toilet,Science Lab Biology Lab

Answer 1

Need to add '()' around the group of 'OR' conditions. Without the brackets, AND will take precedence over OR. Refer to SQL Logic Operator Precedence.

Try this:

SELECT f.FACILITYNAME,COUNT(*) AS count_times, EXTRACT (MONTH FROM b.Timebooked ) AS MONTHS
FROM BookingTable b
JOIN FacilityTable f ON b.FACILITYNO = f.FACILITYNO
WHERE TO_CHAR(b.Timebooked , 'YYYY') = 2018
AND (f.FACILITYNAME = 'Toilet' OR f.FACILITYNAME = 'Science Lab' OR  f.FACILITYNAME = 'Biology Lab')
GROUP BY (EXTRACT (MONTH FROM b.Timebooked )),f.FACILITYNAME
ORDER BY (EXTRACT (MONTH FROM b.Timebooked ));

Without the brackets, your query is actually doing this (which is incorrect):

SELECT f.FACILITYNAME,COUNT(*) AS count_times, EXTRACT (MONTH FROM b.Timebooked ) AS MONTHS
FROM BookingTable b
JOIN FacilityTable f ON b.FACILITYNO = f.FACILITYNO
WHERE ( TO_CHAR(b.Timebooked , 'YYYY') = 2018
AND  f.FACILITYNAME = 'Toilet') OR f.FACILITYNAME = 'Science Lab' OR  f.FACILITYNAME = 'Biology Lab' 
GROUP BY (EXTRACT (MONTH FROM b.Timebooked )),f.FACILITYNAME
ORDER BY (EXTRACT (MONTH FROM b.Timebooked ));