SQL select data based on a one to many relation

by IEE1394   Last Updated August 01, 2020 14:26 PM

i struggle a little with the following database entity relations:

enter image description here

I like to create a query where i get all events for an user that is eighter the owner (item-user_id) or an participant (participant-user_id)

I got back to native queries, cause they are more easy to try out. But the one to many relation between the registration and the participant wont work for me.

Tried joins, subquerys in ther where cause and unions .. but nothing worked so far. Here are some examples:

First the one with the union -> but it returns not the correct result

select e.id, e.has_location, e.has_registration, e.parent_id, e.published, e._end, e.start
from event e
         inner join item i on e.id = i.id
where i.user_id = 2 and start >= '2020-08-01T00:00:00'
union
select e.id, e.has_location, e.has_registration, e.parent_id, e.published, e._end, e.start
from event e
         inner join registration_participants r on r.registration_id = e.id
         inner join participant p on r.participants_id = p.id
where p.user_id = 2
  and e.has_registration
  and p.status != 'CANCELED'
  and start >= '2020-08-01T00:00:00'
order by start;

than the one with some sub query -> but result is also wrong

select e.id, e.has_location, e.has_registration, e.parent_id,
e.published, e.start, e._end from event e
         inner join item i on e.id = i.id where i.user_id = 2    or (select p.user_id
       from participant p
                inner join registration_participants r on e.id = r.registration_id
       where r.participants_id = p.id
         and p.status != 'CANCELED'
      ) = 2 order by e.start


Related Questions



Spring Boot JPA Schema Initialization

Updated April 30, 2018 07:26 AM

Can Spring-JPA work with Postgres partitioning?

Updated May 21, 2018 12:26 PM