Check if records from one table have same column value in two different dates from another table-CodePudding

I need to check which records are related to another record that has the same value in two different dates (any date from 2021 and 2022)

Check which patients were attended in the same medical UNIT in 2021 and 2022.

These are the tables:

PATIENT

PAT_ID	NAME	LASTNAME	LASTNAME2
PK	VARCHAR	VARCHAR	VARCHAR
PK	VARCHAR	VARCHAR	VARCHAR

ATTENTIONS

ATT_ID	ATT_DATE	MED_ID
PK	DATE	FK
PK	DATE	FK

MEDIC

MED_ID	UNIT_ID
PK	FK
PK	FK

UNIT

UNIT_ID	NAME_UNIT
PK	VARCHAR
PK	VARCHAR

I managed to obtain the desired result using INTERSECT between two queries (I'm not totally sure if it's the best solution)

SELECT P.NAME|| ' ' || P.LASTNAME|| ' ' || P.LASTNAME2 AS PATIENT,  U.UNIT_NAME AS UNIT
FROM ATTENTIONS
JOIN MEDIC M USING (MED_ID)
JOIN UNIT U USING (UNIT_ID)
JOIN PATIENT P USING (PATID)
WHERE EXTRACT(YEAR FROM ATTETIONDATE)=2021
INTERSECT
SELECT P.NAME|| ' ' || P.LASTNAME|| ' ' || P.LASTNAME2 AS PATIENT,  U.UNIT_NAME AS UNIT
FROM ATTENTIONS
JOIN MEDIC M USING (MED_ID)
JOIN UNIT U USING (UNIT_ID)
JOIN PATIENT P USING (PATID)
WHERE EXTRACT(YEAR FROM ATTETIONDATE)=2022
ORDER BY 1;

RESULT:

PATIENT	UNIT
PATIENT_NAME1	UNIT_NAME
PATIENT_NAME2	UNIT_NAME
...	...

However I need to do this using only JOIN operators (any of them.. LEFT, RIGHT, INNER, etc)

Any advice on how to do it? Thanks!

CodePudding user response：

You could do it with a single pass through the table and counting the occurrence s of the year, eg

select *
from (
SELECT P.NAME|| ' ' || P.LASTNAME|| ' ' || P.LASTNAME2 AS PATIENT,  U.UNIT_NAME AS UNIT, 
  count(case when EXTRACT(YEAR FROM ATTETIONDATE)=2021 then 1 end) has_2021,
  count(case when EXTRACT(YEAR FROM ATTETIONDATE)=2022 then 1 end) has_2022
FROM ATTENTIONS
JOIN MEDIC M USING (MED_ID)
JOIN UNIT U USING (UNIT_ID)
JOIN PATIENT P USING (PATID)
WHERE ATTETIONDATE between date '2021-01-01' and '2022-12-31'
group by P.NAME|| ' ' || P.LASTNAME|| ' ' || P.LASTNAME2 ,  U.UNIT_NAME 
)
where has_2021 > 0 and has_2022 > 0