I Have a problem creating a base query from a flat db

English
#1

I have a flat db copied from a calc table … a family contact list.
I would like to create a sorted list based on first name (of both the primary and spouce first names appended with their last). The appending part I’ve got.
Lets say I have a table with two families. Family 1 is Bob and Mary Jones. Family 2 is Dick and Jane Doe.
I would like to create a query that would create a list of names sorted on by first name. It would look like this:
Bob Jones
Dick Doe
Jane Doe
Mary Jones

I did create two views (primary name & spouce name) and thought about trying a UNION query but cant puzzle it out.

Any thoughts on how to go about this?
Thanks!

btw, I’m running LibreOffice 24.2.4.2 on a MacBook Pro (13-inch, Mid 2012).
Irvin.

#2

What is a “flat db”?
Sorting by primary name is no problem here:
Create a query by GUI, click on sorting in the field “primary name”.
There is no need for UNION or something like this.

#3

Possibly, your table is linked to rather than copied from Calc. Have a look at the status bar of your database document.

SheetBase
SheetBase559×642 57.8 KB

#4

Goodday Villeroy,

As long as I remember, a “flat DB” is a kind that does not need particular relations, in other words a one-table-base.

That is probably because, thirtyfive years ago, users may run a program that allowed to switch from spreadsheet-line to form-record display…

If Mr."I am from Graal " could stop making his show and post his example, we may perhaps help…

Regards

#5

Without knowing the type of database connection, it is impossible to give any advice about what is possible and what not.

#17

Coding is as much about structuring the source data as it is about building the procedures to extract meaningful information from your data. I see that you are getting some help, and I don’t want to dismiss that. As a thought experiment, and to help you focusing on “keeping it organized”, it is a useful exercise at worst and a perfect solution at best.

Take this as a side comment.

I guess the root of the problem is that you describe a multidimensional structure (your friends/relatives and their family connections) by a flat (one dimensional) dataset, and want to extract the multiple dimensions from the flattened data.

A case study: If Dick dies, what happens to Jane? What if Jane then takes back her maiden name, and marries George o’Malley (and they each keep their surname)? What about Dick/Jane’s children? What about Geourge’s children?

My point:

  • For a common contact list of maybe 10-15 family groups, you can just as well do this manually.
  • If you need to maintain an extended family contact list, to 4th cousins and multiple generations, you are easily past a hundred names. Automating the tasks would be a great help.
    In this situation you need to structure your data so that it reflects the interconnections of the real world. It requires some skill, but the alternative will become an unmanageable mess before you know it.
#6

@RobertG A flat db is a db where all info is put into a single table, as opposed to a relational (rationaized) db where info is divided into tables which contain related info.

@Villeroy I copied the data from a Calc table and pasted into the Base tables. regardless, I was trying to sort on data from two columns as if they were one. I now think that can’t be done.

It’s a HSQLDB.

I have thought about this some more and have decided to rationalize the data into three related tables; last name & address, first name & cell & email, and children. Doing this I should be able create a list by family, and a second list by first name. I will let you know how it goes.

#7

Both names concatenated:

SELECT "Forename" || ' ' || "Surname" AS "Full Name" 
FROM "Table" 
ORDER BY "Full Name" ASC
#8

Won’t work because I want the spouses name too … select “firstname” || ’ ’ || “surname” as “fullname”, “spouse” || ’ ’ || “surname” as “fullname” … I don’t think you can have both with the same name can you.
Again, I think the problem lies in the data being stored in a flat vs relational db.

#9

Create a marriage table with pairs of person IDs.

#10

As you already use HSQLDB your data is stored in one table of a relational db.

But your idea seems to hint you have more than one person in a row like

firstname, surname, spouse

If my theory is right you will indeed need a UNION:

SELECT "Firstname" || ' ' || "Surname" AS "Full Name" 
FROM "Table" 
UNION
SELECT "Spouse" || ' ' || "Surname" AS "Full Name" 
FROM "Table" 
ORDER BY "Full Name"

And you will need to use IFNULL or COALESCE, if there are empty fields for Spouse or Firstname…

#12

https://www.mediafire.com/file/l3n161t1yvy2604/Headshots2.zip/file
Open the “Spouse” form in the Database_HSQL.odb.

#11

@Wanderer That was my original thought, I had tried UNION with no success. I cut and pasted your query, changed the field and table names, & got this error:

The given command is not a SELECT statement.
Only queries are allowed.


SELECT "First" || ' ' || "Last" AS "Full Name"
FROM "Families"
UNION
SELECT "Spouse" || ' ' || "last" AS "Full Name"
FROM "Families"
ORDER BY "Full Name"
#13

Please use this SELECT statement, and, I think it should work for you.

SELECT A.* FROM (  -- This is a comment and line added by Sliderule
SELECT "First" || ' ' || "Last" AS "Full Name"
FROM "Families"
UNION
SELECT "Spouse" || ' ' || "last" AS "Full Name"
FROM "Families"
ORDER BY "Full Name"
) as A  -- This is a comment and line added by Sliderule

Explanation:

  1. I added two lines to your SQL Query, first line and last line.

  2. Reason, for LibreOffice Base Parser to ‘accept’ a UNION or UNION ALL Query, it must be within a sub-query as above ( in this case, I called it A ).

  3. This is an issue of the LibreOffice Base Parser, NOT the database back-end, in this case, HSQL Version 1.8.2

#16

I use the “direct SQL” switch (rightmost icon in toolbar while editing) in this cases to avoid interpretation by LO. But I forgot to mention this above, as it was guesswork anyway…

#14

@sliderule1 Sanford, This threw an error:
The data content could not be loaded.
Table not found in statement [SELECT A.* FROM (
SELECT “First” || ’ ’ || “Last” AS “Full Name”
FROM “Families”
UNION
SELECT “Spouse” || ’ ’ || “last” AS “Full Name”
FROM “Families”
ORDER BY “Full Name”
) as A]

#15

In the SQL you provided above . . . EXACTLY AS YOU TYPED ( entered ) IT . . . your TABLE NAMES and COLUMN NAMES are surrounded by curly double quotes . . . for example . . . “ and ” rather than " .

Assuming the table name and column names as I entered it is correct, just copy and paste the SQL Query as I entered the SQL Query .

Example: there is a difference between:

FROM “Families”

    and    

FROM "Families"

Please note: the same is true for the SINGLE QUOTE . . . should NOT be ’ but rather '

One additional modification . . . the placement of the ORDER BY clause should be at the end

SELECT A.* FROM (  -- This is a comment and line added by Sliderule
SELECT "First" || ' ' || "Last" AS "Full Name"
FROM "Families"
UNION
SELECT "Spouse" || ' ' || "last" AS "Full Name"
FROM "Families"
) as A  -- This is a comment and line added by Sliderule
ORDER BY "Full Name"

Sliderule

#18

The site software automagically (annoyingly to some) converts straight quotes which aren’t in code blocks to curly quotes.

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