[Solved] Need help with a simple query

Creating tables and queries
Post Reply
IHaveNoName
Posts: 20
Joined: Sun Apr 30, 2017 1:37 am

[Solved] Need help with a simple query

Post by IHaveNoName »

Either I'm an idiot, or Base just hates me. This time around, I'm trying to do a query. I want to do a simple search for a customer name, but the only thing I can pull up is the entire list of names - not one or two or whatever. I did it through the wizard - choose CustID, first name, and last name. If I type anything into the search conditions (like, say, the last name I want to find), and then run the query, it throws an error:
The SQL command leading to this error is:

SELECT "Customer Info"."LastName" AS "LastName", "Customer Info"."FirstName" AS "FirstName", "Job Info"."JobNumber" AS "JobNumber", "PhoneNum"."PhoneNum" AS "PhoneNum" FROM "Customer Info" "Customer Info", "Job Info" "Job Info", "PhoneNum" "PhoneNum" ORDER BY "Customer Info"."LastName", "Customer Info"."FirstName", "Job Info"."JobNumber", "PhoneNum"."PhoneNum"
I don't know why it's listing all those other entries, because they're not part of the query.

Edit: I also tried editing the query directly, as per this site: http://inpics.net/tutorials/base2/queries14.html. That didn't throw an error, but it didn't return any results, either - all I got was a blank table.
Last edited by IHaveNoName on Fri May 26, 2017 4:41 am, edited 1 time in total.
Open Office 4.1.3 on Windows 7
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Need help with a simple query

Post by UnklDonald418 »

Either I'm an idiot, or Base just hates me.
No, but you must learn the basics of SQL. If you do a search online there are a number of free SQL tutorials.
The query you show contains what is known as a Cartesian join. The result set of that query will have the number of rows in "Customer Info" times the number of rows in "Job Info" times the number of rows in "PhoneNum". For instance 25 customers times 100 jobs times 40 phone numbers would generate a list of 100,000 rows!
Your query has no WHERE clause to put some constraints on the results. To begin with you need to define the relationships between the tables by adding the following to your query.

Code: Select all

WHERE "PhoneNum"."CustID" = "Customer Info"."CustID" AND "Job Info"."CustID" = "Customer Info"."CustID"
This will dramatically reduce the number of rows returned, but there will still return many rows so you need further constraints. For instance, if you want to find just customer 123 then you would add

Code: Select all

AND "Customer Info"."CustID" = 123
to the WHERE clause. Or you could instead add

Code: Select all

AND "Customer Info".”FirstName” = 'John'
to display all the customers whose first name is John.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
IHaveNoName
Posts: 20
Joined: Sun Apr 30, 2017 1:37 am

Re: Need help with a simple query

Post by IHaveNoName »

UnklDonald418 wrote:
Either I'm an idiot, or Base just hates me.
No, but you must learn the basics of SQL. If you do a search online there are a number of free SQL tutorials.
Fair point, but I'm not the only one using this DB, so I want it to be as easy as possible. That's what the wizard is for, no?
The query you show contains what is known as a Cartesian join. The result set of that query will have the number of rows in "Customer Info" times the number of rows in "Job Info" times the number of rows in "PhoneNum". For instance 25 customers times 100 jobs times 40 phone numbers would generate a list of 100,000 rows!
I was playing around with the query and figured out how to narrow down the search range without SQL - if I add a table and draw links, I can pull up a list of people as long as they fit all the criteria - a link to the Phone Number table means they have to have one listed; likewise for Job Number. I was having issues with Criterion not showing up (all I got was a blank table) because I thought it had to be in quotes, but it doesn't. Once I cleared that hurdle, I altered the criterion a few times to get a feel for it, and it's all working great. I guess I just needed a clear head to figure it all out - it's been a rough week - but your explanation did help, once I could think well enough to apply it.

Again, thanks for being patient with me. :super:
Open Office 4.1.3 on Windows 7
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: (Solved) Need help with a simple query

Post by UnklDonald418 »

If first you use Tools->Relationships and define the links between the tables then the Wizard will know to add the WHERE clauses defining those relationships.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
IHaveNoName
Posts: 20
Joined: Sun Apr 30, 2017 1:37 am

Re: (Solved) Need help with a simple query

Post by IHaveNoName »

UnklDonald418 wrote:If first you use Tools->Relationships and define the links between the tables then the Wizard will know to add the WHERE clauses defining those relationships.
Yeah, that was the part I figured out - I was looking at the tables, and then I drew links and it all fell into place.
Open Office 4.1.3 on Windows 7
Post Reply