[Solved] Extract data from many-many

Discuss the database features

[Solved] Extract data from many-many

Postby AfTech54 » Fri Feb 24, 2017 11:32 pm

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.

Table setup.JPG
Tables setup (scheme?)


qPhotosWithSpecPersons.JPG
Get the ID_Photo


//Roger
Last edited by AfTech54 on Sat Feb 25, 2017 9:12 pm, edited 1 time in total.
Ooo v4.1.2, Windows 10
AfTech54
 
Posts: 28
Joined: Tue Dec 31, 2013 10:08 am

Re: Extract data from many-many

Postby chrisb » Sat Feb 25, 2017 3:13 pm

AfTech54, the code below should do the job.
Code: Select all   Expand viewCollapse view
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.6 & LibreOffice 6.0.7.3 (x64) using HSQL 1.8.10 (Embedded) and HSQL 2.4.1 (Split) on Windows 10
chrisb
 
Posts: 177
Joined: Mon Jun 07, 2010 4:16 pm

Re: Extract data from many-many

Postby AfTech54 » Sat Feb 25, 2017 9:11 pm

THANKS again Crisb!!!
Ooo v4.1.2, Windows 10
AfTech54
 
Posts: 28
Joined: Tue Dec 31, 2013 10:08 am


Return to Base

Who is online

Users browsing this forum: No registered users and 2 guests