How to compare results of different queries in Base?

I am a beginner trying to learn LIbreOffice Base.

The database I am working with contains about 2500 records related to music cds, each record has four fields: ID, group, title, location. There are three possible locations: file, jcs, or a unique code representing a spool location.

Any given music cd (group and title) could potentially have one, two, or all three locations. For instance:

ID, group, title, location
• ID1 Alan Seale Child Of The Moon file
• ID2 Alan Seale Child Of The Moon H20
• ID3 Albert Castiglia Keepin On file
• ID4 Albert Collins Cold Snap jcs
• ID5 Albert Collins Cold Snap file
• ID6 Bill Frisell Blues Dream file
• ID7 Bill Frisell Blues Dream G32
• ID8 Bill Frisell Blues Dream jcs

In the above list for instance, the music cd by Alan Seale titled Child of the Moon is in both the "file" location and the unique "H20" spool location. The music cd by Albert King titled Cold Snap is in the location "jcs" and the location "file". The music cd by Bill Frisell titled Blues Dream is in all three locations, but note the spool location is unique- G32.

I want to be able to write a dynamic query (or report) showing which albums are duplicated and in which location.

I can write a query listing which albums are in which locations. But I can't figure out how the following:

<nl>

• Which albums are in jcs but not in file
• Which albums are in file but not in jcs
• Which albums are in one of the many spool locations but not in either jcs or file. </nl>

How to write a query showing comparisons between records which have one or two fields the same? Merely sorting them is not what I want, because then I have to manually looks through the arrangements in the 2500 record long list.

Thanks if you can point me in the right direction to take...

Keith

• edit retag close merge delete

Sort by » oldest newest most voted

You can probably develop a query for this (probably not worth the time) but since you mentioned a report this was the quickest. Took longer to copy data to table than to create report.

Data Table in (made sure sequence wasn't apparent):

Table Definition:

Report Design:

This is just table in & report out using grouping. You can certainly do more like multiple columns or row coloring if wanted. See documentation ( click here) for more information on report generation.

Edit:

SQL solution has not been much of a success. Based upon the table data above, I was able to create the following:

The result would allow the searches you are looking to accommodate. However, there is a problem. This query was created using HSQLDB v2.3.3 and the one built in with Base is v1.8. With v1.8 I was only able to get one "Join" to work:

Anything else tried produced errors. So it leaves you a couple of choices.

Either find a way to expand upon the SQL for 1.8 (already spent hours on that) or convert your existing DB to a split DB using HSQLDB 2.x and then can use the working SQL above.

I would definitely recommend going to a split DB (see this post for instructions) to avoid possible data loss which can happen with the embedded DB that comes with LO.

Edit 2/27/17:

Although I would still recommend moving to a split DB, there is a way to accomplish the same end result as in HSQLDB v2.x. The major issue with v1.8 seems to be the use of multiple "Right Joins" (in the way needed here) in a query or even when based upon a query containing them. A solution was to create three separate table "Views" and then use those "Views" to create the final "Query" combining them into the same result as I displayed for v2.3.3 above. You can then use this query as a basis for your selection criteria.

A sample is attached with the original table, three Views, and the one query needed for selecting what is wanted.

Sample: CDListings.odb

Edit 2/28/17:

First, make a copy of your .odb file so you have a backup. With embedded DB containing that many records this should be normal practice. Now working with both your .odb & the new Modified.odb (posted below) open both to Tables section. In the Modified.odb, one at a time, right click on a View (looks similar to a table), select "Copy", then in your .odb right click in your table area and select "Paste". Next do the same for each of the queries. Save your .odb and close the Modified.odb.

Now for the results. Run Query SELECTFILENOTSPOOL and you should get a list of records (which is based on COMBINEDVIEWS query) of those albums containing "file" but NOT "spool". This is the direction to use to create the other choices you ...

more

1

Hi- Thanks very much for taking the time to suggest an solution. Unfortunately, that report does not satisfy my requirements. It gives a 600 page report that I need to visually inspect, effectively no better than a simple sort.

For instance, I want to ask the question: which albums are in jcs but not in file? The answer should be only those few records which answer the question.

That's why I was thinking a query might work. If a report, then it should only be a few lines.

Thanks, Keith

( 2017-02-27 02:50:10 +0200 )edit
1

@keithostertag That seems like a lot of pages for 2500 records (only 4 to a page - mine had 8 on half page). Sounds like a possible grouping/sorting problem. I'll see about some SQL for you.

( 2017-02-27 03:22:40 +0200 )edit

HSQLDB v1.8 was still bugging me. Found a solution involving Views. Will edit my answer with explanation & sample shortly.

( 2017-02-27 20:21:57 +0200 )edit

Thank you! Because I'm a beginner at this, most of what you show is still over my head, but it gives me a direction to study and learn. I have to say I am surprised that my goal seems so complex to work out, since to me it seemed fairly straightforward. I guess that's part of learning the limitations of the software. It may take me awhile, but I will post back when I make some progress. Thanks again! You have been very helpful!

( 2017-02-27 22:03:27 +0200 )edit

I want to add that I changed the field values for the spool locations so that they now all start with "spool-", for instance "spool-H20" and "spool-G32" instead of just "H20" and "G32". This will make it easier to make queries based on criteria such as LIKE 'spool-'. To do that I needed to use Tools>SQL, then the statement: UPDATE "tbl_complete" SET "Location" = REPLACE("Location", 'spool-', '') WHERE "Location" LIKE '%spool-%'

( 2017-02-28 03:23:36 +0200 )edit

I'm looking at this URL on StackOverflow that seems to be related:

http://stackoverflow.com/questions/13...

but it's still a bit over my level of understanding...

( 2017-02-28 03:37:14 +0200 )edit

@keithostertag I really think you need to understand the resulting query you have. You comment about changing "spool" to me is a waste of time & effort. You can already do what you wanted with very simple statements: SELECT * FROM COMBINEDVIEWS WHERE (SPOOL IS NOT NULL) AND (JCS IS NULL) gets all records with entries in SPOOL but not in JCS. That is why it sometimes takes time to develop some of these queries. Looking at what it is to be used for and making the result effective.

( 2017-02-28 03:50:55 +0200 )edit

I guess I am also surprised you are still searching for answers when what has been presented is the complete answer - or is there something I didn't comprehend. You could probably take your table data, drop it into the sample provided and have it all working within minutes provided the field types are comparable.

( 2017-02-28 04:38:31 +0200 )edit

Thanks! This is all new to me, so I'm learning. Haven't looked at the split DB yet, but I can now (finally!) use your Query5 to produce similar results with my real data- it gives a 2000 line list (because there are about 2000 different albums). Is there an easy way to modify Query5 to suppress those lines showing albums (GROUP-TITLE) with 'file" location only? for instance, in your above Query5 output I don't want to see the "Albert Castiglio-Keepin On" or the "Albert Colllins-Cold Snap" lines.

( 2017-02-28 17:03:37 +0200 )edit

If you read the answer, Query5 is a FAILED attempt to do in v1.8 what Query1 did in v2.3.3 of HSQL. The answer to Query5 is in the product presented in the sample. The sample will produce the same result in v1.8 as Query1 did in v2.3.3 but it needed to take a different approach to get to the same result. What needs to be using for your selection is the Views & the Query COMBINEDVIEWS in the sample to make it work. You can then use SQL as presented in comment above for selection.

( 2017-02-28 17:21:34 +0200 )edit