Hi!
I'm trying to extract information out of my photo data. I have a Photos, PhotoPerson and Person table where I want to extract all posts in Photos with source (not the ID) and designition where e.g two person is on the photo.
"Show all photos designations and/with sources where my mother AND father appear"
I've come so far that I at least can ectract the ID_Photo. For me it seems tricky to extract readable information i.e. source and photo designation. This is what I need to find my .jpg files.
Anyone that could help me with the sql and please explain how it works.
//Roger
[Solved] Extract data from many-many
[Solved] Extract data from many-many
Last edited by AfTech54 on Sat Feb 25, 2017 9:12 pm, edited 1 time in total.
Ooo v4.1.9, Windows 10
Re: Extract data from many-many
AfTech54, the code below should do the job.
Code: Select all
select "ID_Photo", "Designation", "Source" from
(
select "ID_Photo", count(*) C --count the number of times named persons are associated with each photo.
from
"PhotoPerson" PP
join "Person" PE on PE."ID_Person" = PP."ID_Person"
where PE."Name" in('Lars Eriksson', 'Elisabeth Lindmark')
group by "ID_Photo"
having count(*) > 1 --count will be equal to 2 when both persons appear in the photo
) A
join (select "ID_Source", "ID_Photo", "Designation" from "Photos") P
on P."ID_Photo" = A."ID_Photo"
join (select "ID_Source", "Source" from "Source") S
on S."ID_Source" = P."ID_Source"
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