SQL Query doesn't work as it should Libre Base HSQLDB

English
#1

I’m getting frustrated because I cannot get the result I want. I have a number of groups and want to list them as individual groups. I’ve modified and polished?? my DB. the general groups table seems to be set up correctly. Listed as I want in the separate groups. When I write the following query it lists everyone in the DB not just the group one. The same with all the groups. I’ve tried with the design query and the SQL but the same result.
Please help

SELECT “PEOPLE”.“First”, “PEOPLE”.“Surname”, “GROUPS”.“ONE” from “PEOPLE”, “GROUPS” where “PEOPLE”.“p_ID” = “GROUPS”.“Group_ID” and “GROUPS”.“ONE” is NOT NULL

#2

Without an example or a screenshot from the relation (Tools → Relationship) I don’t understand:
There is a table “GROUPS”. In this table is a column “ONE”. Then you are writing about a ‘group one’. Is this identical?

SELECT "PEOPLE"."First", "PEOPLE"."Surname", "GROUPS"."ONE" 
from "PEOPLE", "GROUPS" 
where "PEOPLE"."p_ID" = "GROUPS"."Group_ID" 
and "GROUPS"."ONE" NOT IS NULL 
ORDER BY "GROUPS"."One"

This are all members of a group in different rows. I you need them in one row it will be tricky with the internal HSQLDB. Works better with internal Firebird.

#3

Yes group one Means Table “Group” Column “One” Sorry it wasn’t clear. I get the same response whichever Column I designate. Your response is the same as I wrote except I wasn’t interested in sorting.

#4

Have resolved it (“GROUPS”.“ONE” = YES) FIXED IT (WITHOUT PARENTHESES)

#5

This raises a question:
since the column only had yes in it if the person was a member of that group, why did ‘IS NOT NULL’ not work? the cell should be empty if not a member.

#6

What kind of field do you use for “GROUPS”.“ONE”? When reading your “Fix” I could only reproduce errors with internal HSQLDB.
If NOT IS NULL won’t work the field isn’t empty. It could be a text content without any character. It could be a boolean field, which is set to ‘no’.

#7

NOT IS NULL

#8

So the syntax was wrong initially should be “not is null” instead of 'Is not null" I’ll keep that in mind for future efforts.
As regardt he field, the text was originally ‘varchar’ i changed it to boolean but that was a disaster so I changed it back. still had the same result, even wiping the table and starting again from scratch. the whole DB is small and I am using it to learn.
The form used to fill the table was a subform and the data was entered using option buttons linked to the columns.
I use the BASE 7.3 guide for reference and the internet so I have a long way to go.
I am progressing slowly but there is improvement so I’ll keep asking questions when i reach a wall i cannot get over
Thanks for all your help

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