I need help with query logic

English
#1

I’ve watched every TheFrugalComputerGuy on queries and I have spent HOURS trying to figure out the Query system’s GUI. Almost every example or tutorial, including the official documentation pretty much only covers SQL instead of the GUI after the basics. I don’t have time to learn SQL right now, and NEED to use the GUI.

What I’m trying to do is to return a list of customers that used our services the previous year(s), but not the current year. The screenshot below is the early version just counting number of jobs done.

baseQuery_Screenshot
baseQuery_Screenshot865×628 36.5 KB

What I’m currently getting is the opposite of what I want. I want a list of people who have 0 jobs done for that time period and its ONLY giving me those with >0. Also its giving me jobs only within 2021, there’s no accounting of IF there are jobs in 2022. I have experience with other programming languages, so I’ve practiced with logic, I’m just not sure how to use the tools here and the GUI confuses me.

Can anyone give me some guidance?
Do I need to do subquries? I can’t seem to find a tutorial that’s not SQL.
How would I even begin to represent the below pseudo code in the GUI?

   if previous years a job was done > 0
      if current year's jobs done = 0
         populate query/report
#2

That describes nicely the conditions visible in your picture:
The first condition leaves only 2021 and later, the second removes all entries from 2022, and I just assume nobody (exept time-travelkers) until now did jobs from 2023 or later otherwise they would not show, but would be counted.

#3

Also seems there is a further logic issue:

.
Negative jobs done?
.
Also, what database are you using through Base?

#5

Maybe “To be done at future date” but that would not work with a simple count…

#6

That was a typo. It should be =0 or <1.
I’m using the standard default one HSQLDB.

#8

I responded to your main reply regarding this. Mostly looking to get exactly what I’ve been returned but be able to show the results of the negation instead. (Who has 0 jobs for this year, rather than those who have). The final query with have user parameters to enter the date, this is just manually entered for getting started.

#4

That’s why I don’t use the GUI much. Have you found the Handbook for Base on the LibreOffice-website?

If I’m reading this right you need a LEFT JOIN of all Ids with your current query.
This will give you counts if all jobs done in 2021 and NULL (empty value, not the number 0) for the other customers.
Then you can add a condition (WHERE in SQL)

"# of jobs" IS NULL

to filter out the customers without jobs.
.
I recommend to avoid space and special chars in names for columns. But maxbe I’m a bit to careful there…

#7

Yeah the official handbook is actually one of the examples I’m talking about. The first half of CH5 includes the GUI, but it just drops off into SQL code examples. The official quick start guide I found to be more useful. But I’m still not sure how to structure my queries to get what I want.

I was hoping I could just set up a query like a function to get just a boolean for the first if, and same with the second if. I’m just having trouble visualizing it.

I REALLY appreciate you sharing your advice about naming conventions! This was just an alias and is for my first stages of attempting to build rather than the final.

Also, yes on the left join. What I’m trying to filter out though is those who have had jobs done the current year, rather than those who have not.

#9

Hello,

This appears to be what you want. Problem is I can only generate SQL - no GUI:

Screenshot at 2022-07-14 15-25-35
Screenshot at 2022-07-14 15-25-35911×500 82.7 KB

SQL used:

Select DISTINCT("JobID") FROM "serviceHistory_dataEdit"
     Where ("JobDate" BETWEEN '2021-01-01' AND '2021-12-31')
                and "JobID" NOT IN (SELECT DISTINCT "JobID" FROM "serviceHistory_dataEdit" 
                   WHERE "JobDate" BETWEEN '2022-01-01' AND '2023-12-31')
#10

This is very close and a good start so thank you. I’m actually looking to have the CustomerID rather than the JobID in the end. The job ids are not unique whereas the CustomerIDs are. I tried modifying the example you gave me but I ran into issues.

Select DISTINCT("CustomerID"), "JobID" FROM "serviceHistory_dataEdit"
     Where ("Date" BETWEEN '2021-01-01' AND '2021-12-31')
                and "JobID" NOT IN (SELECT "JobID" FROM "serviceHistory_dataEdit" 
                   WHERE "Date" BETWEEN '2022-01-01' AND '2022-12-31')

When I use this, and then double check with the profile two things are happening

  • For some reason, ONLY JobID’s 60+ are showing up in the results
  • The results are pretty much what I had before. Those who have done business in the current year as well as last year are still in the list. It should only be those who had service the previous years, but not the current year.

Could you also explain why that couldn’t be done in the GUI?

#11

GUI is limited. I don’t like it and hardly use it (did try here for this answer). A waste of time for me. Databases are based on SQL.
.
My posted sample show the correctly selected records (whether the name is CustomerID or JobID is not relative - each are columns).
.
If what you posted is what you used, you only modified part of the SQL. Try

Select DISTINCT("CustomerID") FROM "serviceHistory_dataEdit"
     Where ("JobDate" BETWEEN '2021-01-01' AND '2021-12-31')
                and "CustomerID" NOT IN (SELECT DISTINCT "CustomerID" FROM "serviceHistory_dataEdit" 
                   WHERE "JobDate" BETWEEN '2022-01-01' AND '2023-12-31')
#12

I hear you on that. I’m just confused that the GUI doesn’t match what can be done with SQL. The whole point of a GUI is to help those with no programming experience or knowledge what’s going on behind the scenes be productive. So far the SQL examples make more sense despite me not knowing it.

Thank you for pointing that out. I tried a few different modifications to see what would happen, and I think I’m still just wrapping my head around how SQL does things. The flow seems different than like python or c/c++.
After doing the changes you suggested it appears to be working like I had hoped. Seeing as I was planning on using User Parameters in my final query, is there a non-GUI way to pass those in?

#14

It is. In SQL you declare wich data you need, not how to get it.
In C you actually would write commands to get the tables line by line.
At the level of your question (one table only) the results may be comparable. When you start to combine tables, just trust the database to find a better way than you - unless you are very talented or your request is not really typical for relational databases.
.
BTW: The gui gives you a quick start, but for a complex szenario you will then start to learn a complex gui. Time better invested in SQL.
.
What I like: You can create a query in the GUI, then open it in SQL to see/learn what was generated. Sadly Base has no pretty-printing of SQL, so it stays hard to read.

#15

This is how Ratslinger’s SQL looks like when you switch from SQL view to design view (with different names for table and columns):

Bildschirmfoto von 2022-07-15 09-38-09
Bildschirmfoto von 2022-07-15 09-38-09860×232 12.1 KB

Things become unmanagable when queries are nested. You have to type most of the query into the tiny boxes of the query designer.
What the query designer is able to deal with is no more than “baby SQL”. The designer covers what you can learn about SELECT statements within 1 hour. Anything beyond the first baby steps is out of reach.

#16

Could you clarify what you mean by “trust the database”? Is that in reference to SQL vs C?


That makes sense as far as the GUI, I guess I just didn’t think what I was trying to do was that complex. I wish the official documentation had a note or notice of the limitations. As a beginner I didn’t really get that impression from the tutorials I had watched/read.

#17

I appreciate you taking the time to set this up and show me a screenshot and explain the limitations! I really thought that what I was doing was “baby SQL”. I guess I got the wrong impression about the GUI. As a beginner it really came off as more “usable with no programming experience” vs “should know SQL under the hood”. Guess its time to learn SQL when I have time.

#18

Thank you so much for your help Ratslinger, I really appreciate it! You’ve helped tempered my expectations. Looks like I’ll need to start learning SQL when I have time.

#19

@Ratslinger I have to take issue with your comment about toy horses - it’s very demeaning to @jumpyGUI. You have been extremely helpful to myself any many other people here and I am sorry that you felt it necessary to lower the tone of the conversation.

jumpyGUI is right in his comment about the point of a GUI, and may not even be aware of the multiplicity of SQL implementations, each with its own peculiarities. The fact that LibO Base is not capable of handling anything but the basics is unfortunate.

I suggest you think less about teaching babies to walk and more about driving a car. 100 years ago, each model was different and you had to learn exactly which levers to move, wheels to turn etc, and you also had to be something of a mechanic in order to drive it. Now it’s been standardised and you can just walk up to a car and drive it away. Can you tell me exactly what goes on when you press the brake pedal, let alone mend it? SQL is still at the first stage. Maybe not a good analogy, but I think better than yours.

#20

Everything is offensive to someone in these days. It was simply an analogy.
.
My Apologies to anyone it may have offended.
.
Comment removed.
.

and now you insult people who can’t remember one thing from another.
.

.
Absolutely for the majority of vehicles both 2 & 4 wheeled.

#21

That’s why I suggested driving a car as the analogy - far more complex than a toy horse and therefore closer to the problems that LibO GUI has to solve. And you obviously know far more about the mechanics of cars than most drivers.

I don’t quite understand that - did you mean ‘distinguish’ rather than ‘remember’? In which case I, too, apologise.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.