How do I JOIN Four similar tables so the records can be processed as though they were one table?

[Jerry_99]

Four or more tables need to be combined such that the query can be run on all tables with one Query to produce DISTINCT text, sums, counts, etc.
I have three queries that process correctly by simply changing the table. (FROM “Table1”) The results are grouped by unique values as sums and counts. This situation is not addressed by any Libre documentation and attempts to implement any JOIN kill both BASE and CALC.
Example:
– Stairs Weight-- Works with “Stairs”, “Decking”, “Frame” and “Guard” tables.
SELECT DISTINCT
“Assembly”,
COUNT( “Weight” ),
“Section”,
SUM( “Weight” )

FROM
“Stairs”
– Adding
– "“Decking”, “Frame” and “Guard” Kills LibreBASE and LibreCalc

GROUP BY
“Assembly”,
“Section”,
“Object type”

HAVING
“Object type” = ‘Part’ OR
“Object type” = ‘Simple Extrude’ OR
“Object type” = ‘ACIS Solid’

ORDER BY
“Assembly” ASC,
“Section” ASC;
Any suggestions??
Thank You - Jerry

[Wanderer]

@Jerry_99

attempts to implement any JOIN kill both BASE and CALC.

I regularly use JOIN in my databases -no problem.
Use the sql-query-editor and select “direct-sql”-mode.
But I’m not sure you need JOIN…
.
When see your

@Jerry_99

FROM
“Stairs”
– Adding
– "“Decking”, “Frame” and “Guard” Kills LibreBASE and LibreCalc

I guess you need a UNION of your 4 tables.
Compare:

W3Schools.com

Maybe create the UNION first as a VIEW in the database, then group this by an additional select.

@Jerry_99

This situation is not addressed by any Libre documentation

Unlike you I did not read everything to really exclude this, but you seem to miss an important point: Base is no database. It connects to databases. And solutions depend on the used database. (As you also omitted the database you use I assume HSQLDB embedded is quite likely.)
So you need to check, if the connected database is capable to do the job.
https://www.hsqldb.org/doc/1.8/guide/


Edit1:
I quickly found an example on ask.libreoffice, so I doubt your “not mentioned” even more:

Base SQL Union Syntax

I originally asked a question about Multiple Sub Reports. I was very politely directed to this question Libre Data - multi level report, which answered my question perfectly, by providing an explanation and this sample database: InvoiceForumPost.odb. The sample database contains a SQL UNION command, and I have a few questions about the syntax. Here is the result: [MaterialsRentalUnion SQL Query] And here is the, annotated, command: [MaterialsRentalUnion SQL Query Command] -I looked up the…

Edit2:
Google found also a wiki-guide on the HSQLDB (includes UNION and JOIN):

Hsqldb_guide.pdf

Just to reference it: Check also JOIN, but it is usually not used to combine similiar tables. It is more to get additional columns via index/ foreign key

W3Schools.com

[CRDF2]

You need to show the data structure.
What are the primary keys of the tables?
Then build the query stepwise…

1. First just one table — verify it works
2. Add another table — […]

@Jerry_99

This situation is not addressed by any Libre documentation

This is a SQL situation. And the specific sintaxe rests on the DB you are using.
Generally speaking, it goes like

SELECT DISTINCT "S"."Assembly", [...]
FROM "Stairs" "S"
JOIN "Decking" "D" ON "D"."ID" = "S"."ID"
JOIN [...]

[fpy]

@CRDF2

ON

USING

[F3KTotal]

SELECT DISTINCT
    "Assembly",
    "Section",
    "Object type",
    COUNT( "Weight" ) AS "COUNT_WEIGHT",
    SUM( "Weight" ) AS "SUM_WEIGHT"
FROM
    (SELECT DISTINCT
        "Assembly",
        "Section",
        "Object type",
        "Weight"
    FROM
        "Stairs"
    UNION SELECT DISTINCT 
        "Assembly",
        "Section",
        "Object type",
        "Weight"
    FROM
        "Adding"
    UNION SELECT DISTINCT
        "Assembly",
        "Section",
        "Object type",
        "Weight"
    FROM
        "Decking"
    UNION SELECT DISTINCT
        "Assembly",
        "Section",
        "Object type",
        "Weight"
    FROM
        "Frame"
    UNION SELECT DISTINCT
        "Assembly",
        "Section",
        "Object type",
        "Weight"
    FROM
        "Guard")
GROUP BY
    "Assembly",
    "Section",
    "Object type"
ORDER BY
    "Assembly" ASC, 
    "Section" ASC

[Wanderer]

@fpy

@CRDF2

ON

USING

?? compare:

SQL | JOIN USING vs JOIN ON

[skyandrews]

I believe that to have multiple tables in the FROM clause then there should be a WHERE clause defining relations between primary keys and foreign keys of all the tables. (WHERE “Decking”.“stairID”=“Stairs”.“ID” AND “Frame”.“stairID”=“Stairs”.“ID” AND …etc)

[Wanderer]

@skyandrews

I believe that to have multiple tables in the FROM clause then there should be a WHERE clause defining relations

Better check syntax before you try this. JOIN can be defined directly in FROM (and as I think this thread is about UNION: No WHERE necessary.)
.
Some examples:

SQL query return data from multiple tables

PS: I’d also suggest you use the speech bubble (comment) for your thoughts, unless you are sure to “solve” the question via “Suggest asolution”.

[skyandrews]

@Wanderer

JOIN can be defined directly in FROM (and as I think this thread is about UNION: No WHERE necessary.)

I am certain there needs to be a WHERE clause in the UNION to specify relations between the tables being joined!

[Wanderer]

@skyandrews

I am certain there needs to be a WHERE clause in the UNION

Do you read the threads, where you comment?
Try to find a WHERE in the UNION posted above by @F3KTotal

How do I JOIN Four similar tables so the records can be processed as though they were one table?

SELECT DISTINCT "Assembly", "Section", "Object type", COUNT( "Weight" ) AS "COUNT_WEIGHT", SUM( "Weight" ) AS "SUM_WEIGHT" FROM (SELECT DISTINCT "Assembly", "Section", "Object type", "Weight" FROM "Stairs" UNION SELECT DISTINCT "Assembly", "Section", "Object type", "Weight" FROM "Adding" UNION SELECT DISTINCT "Assembly", "Section", "Object type", …

A UNION can combine completely unrelated data, if the same columns are used.

[skyandrews]

@Wanderer

Do you read the threads, where you comment?

No, I don’t! Only posts from the originator.