CODEWITHSUNDEEP
sqloracle-database
dace
dace

Reputation: 124

Check if specific record exists in one to many tables

I have two tables with one-to-many relation with such structure: 

meeting              meeting_person
+--+------------+    +--+----------+-----------+---------+
|id|meeting_type|    |id|meeting_id|person_type|person_id|
+--+------------+    +--+----------+-----------+---------+
|1 |          1 |    |1 |         1|          1|     100 |
|2 |          2 |    |2 |         1|          1|     101 |
+--+------------+    |3 |         1|          2|     102 |
                     |4 |         2|          3|     103 |
                     +--+----------+-----------+---------+

What I want to do is check whether the specific record exists before inserting, and if so warn user about it.
The thing is I need to check whether a meeting with such meeting_type and person_id with person_type exists, and that for all persons.
For example, if with given data I wanted to insert meeting with meeting_type = 1 and such meeting_persons:

Upvotes: 0

Views: 2542

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

You want to know if a meeting with the meeting type and the persons in question already exists. So join and count:

select count(*)
from
(
  select m.id
  from meeting m
  join meeting_persion mp on mp.meeting_id = m.id
  where m.meeting_type = 1 
    and (mp.person_type, mp.person_id) in ((1,100),(1,101),(2,102))
  group by m.id
  having count(*) = 3
);

This query results in the number of matching meetings (0 or more).

If, however, you are only interested in meetings with exactly those persons, i.e. no additional persons, then you must move the criteria on persons from WHERE to HAVING:

select count(*)
from
(
  select m.id
  from meeting m
  join meeting_persion mp on mp.meeting_id = m.id
  where m.meeting_type = 1 
  group by m.id
  having count(case when (mp.person_type, mp.person_id) in ((1,100),(1,101),(2,102))
                    then 1 end) = 3
     and count(*) = 3
);

This query results in the number of matching meetings (0 or 1).

Upvotes: 1

Pablo notPicasso
Pablo notPicasso

Reputation: 3161

How about:

WITH MEETING(ID, MEETING_TYPE) AS
 (SELECT 1, 1 FROM DUAL
  UNION SELECT 2, 2 FROM DUAL),
MEETING_PERSON(ID, MEETING_ID, PERSON_TYPE, PERSON_ID) AS
 (SELECT 1, 1, 1, 100 FROM DUAL
  UNION SELECT 2, 1, 1, 101 FROM DUAL
  UNION SELECT 3, 1, 2, 102 FROM DUAL
  UNION SELECT 4, 2, 3, 103 FROM DUAL)
SELECT CASE WHEN COUNT(*) = 0 THEN 'Yes' ELSE 'No' END AS show_warning FROM meeting, meeting_person 
WHERE meeting.id = meeting_person.meeting_id
AND meeting.meeting_type = 1 
AND (person_type, person_id) NOT IN ( (1,100),(1,101),(2,102) ); -- comment this and uncomment second line for second example to check
--AND (person_type, person_id) NOT IN ( (1,100) );

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

As phrased, you need to use a trigger for this. I might suggest changing the data structure to include meeting_type in meeting_person. Yes, I know that violates normal form. But if you had it, you could enforce your logic with a simple constraint:

alter table meeting_person add constraint unq_meetingperson_person_type
    unique (person_id, meeting_type);

How can you do this safely? Use foreign key relationships:

alter table meeting add constraint unq_meeting_type_id
    unique (type, id);

alter table meeting_person add constraint fk_meetingperson_meetingtype_id
    foreign key (meeting_type, id) references meeting(meeting_type, id);

This does incur extra space for the additional (unnecessary) indexes. It does require including one extra column in meeting_type. But it does allow you to implement this logic without using triggers.

Upvotes: 1

Related Questions