[Solved] Report out of many-many relation

Creating tables and queries
Post Reply
AfTech54
Posts: 64
Joined: Tue Dec 31, 2013 10:08 am

[Solved] Report out of many-many relation

Post by AfTech54 »

Hi!

Thanks to Villeroy I have at least been able to store information of my photo collection in Base. Now I'm struggling with creating reports as I get multilines in my query becuse I have 1-n persons in a photo.

I would like to have information from one photo in a one or a two (dependig of number of persons) liner.

I've attached the relations, a query of what information i want to have and how I want the report to look like. Any other proposal can be accepteable as long as I don't get reduntant information, but my first objective is a oneliner.

The query with multi row result, which is not desireable in a report.
What information I want to have
What information I want to have
Thanks
Roger
Attachments
Ex of desired report.odt
Desireable report
(11.37 KiB) Downloaded 223 times
How the tables are related
How the tables are related
Last edited by AfTech54 on Wed Feb 08, 2017 8:46 pm, edited 3 times in total.
Ooo v4.1.9, Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Report out of many-many relation

Post by Villeroy »

The relation Person--SourcePerson--Source is not in the query. It would add another limitation to the record set.
You may also use DISTINCT. SELECT DISTINCT ... <and all the rest>
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
AfTech54
Posts: 64
Joined: Tue Dec 31, 2013 10:08 am

Re: Report out of many-many relation

Post by AfTech54 »

Thanks!

The relation Person--SourcePerson--Source, owner(s) of an album, should not be in this query I think. Because in this report I'm not interested in that information other than that a photo belongs to specific photo album. I tried DISTINCT but it gave me the same result.

I'm thinking of using write to create the reports. As I'm having ancestor research as a hobby, my thoght was, among other purposes with the reports, to add reports as attachments to persons., That's a little bit easier because I just get a 1-n relation person - photo (1 row/photo). However as I'm sharing albums I would like to have a report per album (info in query) as well and then I'll get 1-n relation regarding photo - persons (1-n rows/photo).

Doesn't I have to do some magic here to get a one or a two liner information for one photo, see attached .odt?

//Roger
Attachments
Ex of desired report.odt
Desired layout
(11.37 KiB) Downloaded 224 times
Ooo v4.1.9, Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Report out of many-many relation

Post by Villeroy »

Try harder. I don't have the time to reconstruct your database from screen shots.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
AfTech54
Posts: 64
Joined: Tue Dec 31, 2013 10:08 am

Re: Report out of many-many relation

Post by AfTech54 »

Opps, sorry still amatuer didn't understand that you needed it:-) Seems to big (>128 Kb) to attach, what can I do?

//Roger
Ooo v4.1.9, Windows 10
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Report out of many-many relation

Post by eremmel »

Your relation from Photos to PhotoPerson is 1 to N, so when you find multiple PhotoPerson per Photo you see entries. But you came already to that conclusion.
What you need is something like an Concatenate function with in aggregation (GROUP BY on all columns except Namn).
Unfortunate this function is not available in the standard (internal) database shipped with AOO Base (see here).
When you make your report with ORB you can define grouping (levels), concatenate all names with a user-defined report function and print all nice on one line.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Report out of many-many relation

Post by chrisb »

Hello AfTech54,

you are attempting to group your data into a single row.
unfortunately the number of names in the field "Namn" is variable which means that your query generates a row for each name processed.
your intention is to concatenate these names thus enabling them to be displayed on a single row.
hsqldb x 2 contains the function 'GROUP_CONCAT' which provides the ability to do just that, therefore stepping up to hsqldb 2.3.4 would be a good move.

it's not impossible to achieve your aims using hsqldb x 1.8 but it does require some work.
using three queries i'm going to show you how.
1. copy the code below & paste it here >>> 'Queries>Create Query in SQL View'.
save as 'qPhotoLogData'.
execute each query as you go to test its validity.
create a view from this query by:-
right click the query & select 'create as view'.
name the view as 'vPhotoLogData'.

Code: Select all

select  P."ID_Photo" P_ID_PHOTO, P."ID_Source" P_ID_SOURCE, P."ID_Location" P_ID_LOCATION, P."Designation" P_DESIGNATION, P."Date" P_DATE,
PP."ID_Photo" PP_ID_PHOTO, PP."ID_Person" PP_ID_PERSON,
PE."ID_Person" PE_ID_PERSON, PE."ShortName" PE_SHORTNAME,
L."ID_Location" L_ID_LOCATION, L."Location" L_LOCATION,
S."ID_Source" S_ID_SOURCE, S."Source" S_SOURCE

from "Photos" P
join "PhotoPerson" PP on PP."ID_Photo" = P."ID_Photo"
join "Person" PE on PE."ID_Person" = PP."ID_Person"
join "Location" L on L."ID_Location" = P."ID_Location"
join "Source" S on S."ID_Source" = P."ID_Source"
2. copy the code below, create a query as in step 1 & save it as 'qShortNameGetPosition'.

Code: Select all

SELECT "P_ID_PHOTO", "PE_SHORTNAME",
( SELECT COUNT( * ) FROM "vPhotoLogData" WHERE "P_ID_PHOTO" = "V"."P_ID_PHOTO" AND "PE_SHORTNAME" < "V"."PE_SHORTNAME" ) "POS" 
FROM "vPhotoLogData" "V"
3. copy the code below, create a query, save as 'qReportQuery' & execute. i don't have your database so it's fingers crossed.
because this is a workround it's not dynamic which means we have to physically cater for the maximum number of names per photo we may encounter.
initially the max rows expected is 4 (POS 0 to 3). if you require more then just copy & paste as required.
i have not used an order by clause because it's not included in your query.

Code: Select all

select distinct S_SOURCE "Kalla", P_DESIGNATION "Foto", P_DATE "Datum", L_LOCATION "Plats",

(select PE_SHORTNAME from "qShortNameGetPosition" where P_ID_PHOTO = V.P_ID_PHOTO and POS = 0)
||
coalesce((select ', ' || PE_SHORTNAME from "qShortNameGetPosition" where P_ID_PHOTO = V.P_ID_PHOTO and POS = 1), '')
||
coalesce((select ', ' || PE_SHORTNAME from "qShortNameGetPosition" where P_ID_PHOTO = V.P_ID_PHOTO and POS = 2), '')
||
coalesce((select ', ' || PE_SHORTNAME from "qShortNameGetPosition" where P_ID_PHOTO = V.P_ID_PHOTO and POS = 3), '')
"Namn"

from "vPhotoLogData" V
where S_SOURCE = 'LEEL 1'
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
AfTech54
Posts: 64
Joined: Tue Dec 31, 2013 10:08 am

Re: Report out of many-many relation

Post by AfTech54 »

Hi guys and Thanks both!

Seems to work perfekt!!! MAGIC!!
I'll add 16 more rows so I'll just copy them.

Sorry I couldn't attach the db, maybe it should have worked if I deleted the row/post.

A couple of abbrevations AOO and ORB?

Update to HSQL 2.3.4 does that mean I'll update the embeddded one (?) that I got with OOO or is this a separate DBM. Is it hard to upgrade or does a happy amateur manage to do it?

I hadn't manage this without your help!! Thanks again! Now I dare to continue to add data into the db. I'll enter the names of the person in the photo from left to right so I assume it's that order I'll get. I will put som hours in trying to understand the code too!

//Roger
Ooo v4.1.9, Windows 10
AfTech54
Posts: 64
Joined: Tue Dec 31, 2013 10:08 am

Re: Report out of many-many relation

Post by AfTech54 »

Hi again Crisb!

Now I realized what you ment with order persons, they are now order in alphabetic order. I was so stupid I thought they should appear in the order (in photo left to right) as I recorded them. Can they they are ordered after your pos instead of name will that help? This demands of course that your poses will be assigned in "my" person recording order.

If not would it help if I add a "Pos" kolumn in PhotoPerson table where I can set order when I record persons to a photo?
Does this require a lot of work to rewrite the code?

Will this do?
SELECT "P_ID_PHOTO", "PE_SHORTNAME", ( SELECT COUNT( * ) FROM "vPhotoLogData"
WHERE "P_ID_PHOTO" = "V"."P_ID_PHOTO" AND "PE_SHORTNAME" < "V"."PE_SHORTNAME" ) "POS"
FROM "vPhotoLogData" "V"
ORDER BY "P_ID_PHOTO", "POS"

No it didn't :( , I think I have to set an order when I record persons to a photo.

//Roger
Ooo v4.1.9, Windows 10
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Report out of many-many relation

Post by robleyd »

TLA translation for you :)

AOO => Apache OpenOffice

ORB => Oracle Report Builder
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Report out of many-many relation

Post by chrisb »

hello AfTech54,

in order to use hsqldb 2.3.4 (most recent version at this time) the database must be split i.e. no more embedded.
there is a step by step tutorial here >> viewtopic.php?f=83&t=65980

the query 'qShortNameGetPosition' enables us to select all persons with a link to "Photos.ID_Photo" (unique photo).
we need to allocate each individual person a sequential value ("POS") which is used to determine the order of a persons appearance in the field "Namn".
i used the value of "PE_SHORTNAME" in order to calculate "POS" which results in a list persons sorted by alphabetical order i.e. 'Anne, George, Jane' etc.

if your wish is to order people by the value of "PhotoPerson.ID_PhotoPerson" then you need to make the following changes:-
1. add PP."ID_PhotoPerson" PP_ID_PHOTOPERSON, to the line ending 'PP_ID_PERSON,' in the select clause of the query 'qPhotoLogData_SQL'. save the query then delete & recreate the view 'vPhotoLogData'.
2. replace 'AND "PE_SHORTNAME" < "V"."PE_SHORTNAME" ) "POS"' with AND "PP_ID_PHOTOPERSON" < "V"."PP_ID_PHOTOPERSON" ) "POS" in the where clause of the query 'qShortNameGetPosition'.

if your issue has been resolved then please prepend '[SOLVED]' to the subject line in your initial post.
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
AfTech54
Posts: 64
Joined: Tue Dec 31, 2013 10:08 am

Re: Report out of many-many relation

Post by AfTech54 »

THANKS agin Crisb!!

Suberb, Perfect ...:-)

//Roger
Ooo v4.1.9, Windows 10
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: [Solved] Report out of many-many relation

Post by chrisb »

no its not perfect.
download the attachment. All it contains is 4 queries (no tables).
you need to drag the tables from your original database into this one.
create a view from the query 'qPhotoLogData_SQL' & save it as 'vPhotoLogData'.
create a view from the query 'qShortName(By_PhotoPerson-ID_PhotoPerson)_SQL' & save it as 'vShortName'.
execute the query 'qReportQuery_SQL'.

you will not see any difference (the data will be the same) but these queries will improve the efficiency of your database.

you may use a maximum of 20 names per photo.
the query 'qShortName(By_PhotoPerson-ID_PhotoPerson)_SQL' orders the names by "PhotoPerson.ID_PhotoPerson" which is your preference.
the query 'qShortName(By_Person-ShortName)_SQL' orders the names by "PersonShortName" which is more logical.

i sincerely believe that you genuinely appreciate the efforts of other forum users & am pleased to have helped in a small way.
PhotoLog Queries.odb
(3.66 KiB) Downloaded 199 times
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
AfTech54
Posts: 64
Joined: Tue Dec 31, 2013 10:08 am

Re: [Solved] Report out of many-many relation

Post by AfTech54 »

THANKS!!!
Ooo v4.1.9, Windows 10
Post Reply