Why does my PostgreSQL query not work as expected?-CodePudding

I currently have two tables called calendars and events in my PostgreSQL database which are joined on calendars.uuid = events.calendar_id.

At present, a person can have more than one calendar in the calendars table, however I need to change this so the person_id has a unique constraint, and hence they should only be able to have one entry moving forward.

I therefore need to identify only the person(s) which currently have more than one calendar and all the associated records from the events table i.e. person_id = 4

calendars:

uuid | person_id
----- ---------------
   1 | 1
   2 | 2
   3 | 3
   4 | 4
   5 | 4
   6 | 4
   7 | 5
   8 | 5

events:

uuid | calendar_id | event_id
----- -----------------------
   1 | 1           | 4728
   2 | 1           | 8942
   3 | 1           | 7842
   4 | 2           | 9784
   5 | 3           | 9852
   6 | 3           | 1298
   7 | 4           | 4983
   8 | 5           | 4892
   9 | 5           | 8522

My query is as follows, however this is not working, and as i'm fairly new to SQL/PSQL I'm struggling to figure this one out:

SELECT
    calendars.uuid,
    calendars.person_id,
    events.uuid,
    events.calendar_id,
    events.event_id
FROM
    events
    INNER JOIN (
        SELECT
            person_id,
            count(*)
        FROM
            calendars
        GROUP BY
            person_id
        HAVING
            count(*) > 1) AS calendars ON calendars.uuid = events.calendar_id

Any help would be much appreciated.

CodePudding user response：

You can join events and calendar then put the person_id in the where clause.

    SELECT 
            calendars.uuid,
            calendars.person_id,
            events.uuid,
            events.calendar_id,
            events.event_id 
    FROM 
        calendars  
    INNER JOIN events 
            ON events.calendar_id = calendars.uuid
    WHERE calendars.person_id in (
       SELECT
           person_id 
        FROM
           calendars
        GROUP BY
           person_id
        HAVING
            count(*) > 1 )
    


uuid    person_id   uuid    calendar_id event_id
4       4            7        4             4983
5       4            8        5             4892
5       4            9        5             8522

CodePudding user response：

I find it helps to structure your query such that you segregate the parts that are most restrictive first. So I would use a cte to restrict the persons to those wanted and then include the cte as an inner join to a standard query. Something like this:

WITH cte as 
    (SELECT person_id
        FROM
            calendars
        GROUP BY
            person_id
        HAVING
            count(*) > 1)
SELECT
    calendars.uuid,
    calendars.person_id,
    events.uuid,
    events.calendar_id,
    events.event_id
FROM
    events
    INNER JOIN calendars ON calendars.uuid = events.calendar_id
    INNER JOIN cte ON cte.person_id = calendars.person_id