[Solved] Query not returning all results

Creating tables and queries
Post Reply
miabrown
Posts: 2
Joined: Wed Jun 15, 2016 5:07 pm

[Solved] Query not returning all results

Post by miabrown »

Please help a newbie!

I have created a basic query which I wish to further breakdown in the future. This query should return ALL records at this time but it isn't for some reason. It appears to be omitting the records which have the company name 'MSL' but I have not specified this.

This is my query
This is my query
This is my query
This is the table the query is looking up
table.jpg
This is the result I am getting
runquery.jpg
Last edited by miabrown on Thu Jun 16, 2016 5:47 pm, edited 1 time in total.
OpenOffice 4.1.2 on Windows 7 Professional
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Query not returning all results

Post by UnklDonald418 »

Look at your data in the two columns “CAR”.”Customer and “Customers”.”Customer” for the missing records. You will probably find that one of them has an extra Space Character (or some other invisible character) or two on the end of one of the strings in those columns. In other words “John Doe” is not the same as “John Doe “, nor is it the same as " John Doe".
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
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Query not returning all results

Post by Sliderule »

I would to add an additional possibility to the wise advice offered by user UnklDonald418.

If you defined the column type, in either ( or both ) columns, "CAR"."Customer" OR "Customers"."CustomerName" as VARCHAR rather than VARCHAR_IGNORECASE . . . the characters between the two columns must match EXACTLY . . . including CASE: UPPER, Mixed, lower .

For example, if VARCHAR . . . 'MSL' is not the same as 'MsL' nor 'msl' . On the other hand, if the two column types are defined as, VARCHAR_IGNORECASE . . . they would match ( be found ). :D

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Query not returning all results

Post by Villeroy »

1) It is a mistake to link both tables by names. You better link them by their unique ID numbers (primary keys).
2) Your query shows all records with matching cars and clients (inner join). It does not show any cars with no client nor client without car. You need an outer join to show all the cars and their matching clients (or blanks in case of no match) or all clients with their matching cars (or blanks in case of no match).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
miabrown
Posts: 2
Joined: Wed Jun 15, 2016 5:07 pm

Re: Query not returning all results

Post by miabrown »

Thanks, I have taken all of your advice and applied it and the query is now finding all records except record '0'. So I replaced all of the date from record '0' into a new record and the query is now finding it. It seemed to happen when I changed the link from names to ID numbers. Out of interest how do I use and outer join? There is no option for outer join on my system - it shows inner join, left join, right join or cross join?! Please remember I am very newbie! :oops:
OpenOffice 4.1.2 on Windows 7 Professional
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Query not returning all results

Post by Villeroy »

Left joins and right joins are outer joins.
All records from the left table:

Code: Select all

SELECT ... 
FROM "CAR" LEFT JOIN "Customers" ON "CAR"."Customer"="Customers"."CustomerName"
equivalent, all records from the right table:

Code: Select all

SELECT ... 
FROM "Customers" RIGHT JOIN "CAR ON "CAR"."Customer"="Customers"."CustomerName"
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply