How to compare results of different queries in Base?

English
#1

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:

  • 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.

    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

    • #2

    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):

    image description

    Table Definition:

    image description

    Report Design:

    image description
    image description871×332 36.2 KB

    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:

    image description
    image description524×635 61.5 KB

    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:

    image description

    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 wanted. DUPLICATERECORDS will list potential duplicate records based upon your main table. ERRORALBUMFIELD should list groups with possible problems in the “Album” field (further SQL is necessary to find actual records). ERRORGROUPFIELD should list albums with possible problems in the “Group” field (further SQL is necessary to find actual records).

    Finally, you don’t need to do anything with tbl_complete in the Modified.odb Tables section. It is necessary for the Views & Queries.

    Modified.odb

    #3

    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

    #4

    @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.

    #5

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

    #6

    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!

    #7

    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-%’

    #8

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

    but it’s still a bit over my level of understanding…

    #9

    @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.

    #10

    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.

    #11

    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.

    #12

    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.

    #13

    You have a great deal to learn about Base. I’m going to attempt to make this as easy as possible to show you this works. I need to know the EXACT field names and field types (with lengths) of your data file.

    #14

    Yes, as a beginner I am new to both terminology and concepts. I don’t see COMBINEDVIEWS anywhere in the CDListings.odb sample. How to check HSQL version? Using LO 5.2.4.2.1+ under Linux.

    I understand Query5 isn't the final product... just taking things little at a time. I wont learn all this at once, and will refer to your suggestions for weeks.

    ID Integer[INTEGER] 10
    Group Text[VARCHAR] 80
    Album Text[VARCHAR] 80
    Location Text[VARCHAR] 30

    #15

    Sorry, still trying to figure out how to format in this comment box!

    #16

    Sorry, but I also need the EXACT name of your table. COMBINEDVIEWS as mentioned is a query. You will find it in the Queries section. The Views are in the Tables section.

    A couple of comments ago you asked for a way to modify Query5. My point is why are you trying to use something which isn’t working correctly? BTW - I’m sure you are using v1.8 HSQL since you can only use a different one if using a split or external DB.

    #17

    Don’t know how I missed seeing COMBINEDVIEWS in the queries, now got it. The name of my table is “tbl_complete”. Is it standard convention to make all the tablenames, fieldnames, and variables in queries all caps as you seem to do? I was looking at Query5 first because it seemed simpler and I thought the split DB solution was an advanced topic I would need to approach gradually after seeing what the “standard” Base configuration could do, not a good idea? Thanks again for your advice.

    #18

    Going to add edit in answer. I use all caps (never spaces or spec char) because it makes it much easier to use in queries & especially macros. With caps you don’t need to surround names with double quotes & macros would need two sets surrounding names.

    #19

    Thanks for all your work! The DUPLICATERECORDS, ERRORALBUMFIELD, and ERRORGROUPFIELD worked as is, but the views did not work with copy/paste- that gave the test data, not the real data! LO was turning them into simple tables, with no sql. So I instead created queries named fileView, jcsView, spoolView and copied the sql into them, then needed to edit the COMBINEDVIEWS appropriately. Now everything seems to work as intended. Lots to learn, and a great tutorial from you, I really appreciate it.

    #20

    Thanks for all your work! The DUPLICATERECORDS, ERRORALBUMFIELD, and ERRORGROUPFIELD worked as is, but the views did not work with copy/paste- that gave the test data, not the real data! LO was turning them into simple tables, with no sql. So I instead created queries named fileView, jcsView, spoolView and copied the sql into them, then needed to edit the COMBINEDVIEWS appropriately. Now everything seems to work as intended. Lots to learn, and a great tutorial from you, I really appreciate it.

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