Topic: using a "Filter" on Sql Report

Hello, i want to report something from an SQL Table, so i use:

LEFT OUTER JOIN orcamento on orcamento.id_assistencia = assistencia.id

The problem is that i want to show every "orcamento" that has the "assistencia.id" but i don't want to see the ones that have a boolean value = 1
imagine that i have orcamento.boolean , and i want to say to the report that i only want to show orcamento.boolean = 0 , or that i don't want to show boolean = 1 , is this possible with the reports?

Many thanks

2 (edited by mathmathou 2017-12-03 19:50:11)

Re: using a "Filter" on Sql Report

Hello Vasco,


I am not sure I got your question right but If you are using a SQL report, just add a WHERE clause after your JOIN like :


LEFT OUTER JOIN orcamento on orcamento.id_assistencia = assistencia.id
WHERE orcamento.boolean = 0

Now, why do you use LEFT OUTER JOIN ?


When you do a SELECT with INNER JOIN like :

SELECT
x
FROM table_a
INNER JOIN table_b

you will get all records that have x in table_a AND a link in table_b


If instead you use LEFT OUTER JOIN, you will get all records that have x in table_a EVEN IF they have no link in table_b.


For example, if you do :

SÉLECT
customer.name,
sex.gender
FROM
customer
LEFT OUTER JOIN sex on customer.id_sex = sex.id

The query will return ALL customer names EVEN if no sex (male or female) has been declared for them. But if you use INNER JOIN you will only get customer names that have a sex declared.


I know this is not the subject of your question, but I was surprised to read that so I ask  smile


Finally, from memory because I am not in front of my computer, if you don’t want to use the WhERE clause in your query, just add the orcamento.boolean in the SELECT part and filter the boolean field to 0 in the report.


Hope this helped and I understood your question right.


Cheers


Mathias

I'm a very good housekeeper !
Each time I get a divorce, I keep the house

Zaza Gabor