[Solved] Extract data from many-many

[AfTech54]

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.

Tables setup (scheme?)

Get the ID_Photo

//Roger

[chrisb]

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"

[AfTech54]

THANKS again Crisb!!!