CODEWITHSUNDEEP
sqlms-accessduplicates
Rezepturdoku
Rezepturdoku

Reputation: 23

Finding duplicates in a 1:n relation

May be a newbie question, but I completely drew blank: I have a prescription database and in it two tables:

**prescriptions**, with amongst others a field ID (primary key)

**lines**, amongst others with fields ID_P (foreign key) and name (my target values)

Those two tables are linked in a 1(prescriptions):n(lines) relation.

I need to find out if a given set of names has already been entered in these tables in this combination, but likely in different order.

Short Example: given a prescription with lines A,B,C, is there already a prescription entered with exactly the lines A,B,C, order B,A,C, or any permutation of these?

My thoughts ended up in inserting a search field in table prescriptions with the content of all fields "name" of table "lines" in it which belong to this prescription, but this seems not to be ideal.

Any idea how to reach this goal?

Sample: table lines:

+----+------+----------------------+
| ID | ID_P |          name        | 
+----+------+----------------------+
| 1  | 1    |Metronidazol          |
| 2  | 1    |Erythromycin          |
| 3  | 1    |Basiscreme            |
| 4  | 2    |Metronidazol          |
| 5  | 2    |Vaseline              |
| 6  | 3    |Erythromycin          |
| 7  | 3    |Clotrimazol           |
| 8  | 3    |Basiscreme            |
| 9  | 4    |Clotrimazol           |
| 10 | 4    |Basiscreme            | 
+----+------+----------------------+

Entering the names Basiscreme + Metronidazol + Erythromycin should end up in a hit, Metronidazol + Basiscreme not. Or: Clotrimazol + Basiscreme is a hit, Erythromycin + basiscreme not.

Upvotes: 1

Views: 60

Answers (1)

McNets
McNets

Reputation: 10807

You can group by name and return those with a count >= 3.

select
    name, count(*)
from
    lines
where
    name in ('Basiscreme', 'Metronidazol', 'Erythromycin')
group by 
    name
having 
    count(*) >= 3;

name       | (No column name)
:--------- | ---------------:
Basiscreme |                3

db<>fiddle here

Upvotes: 1

Related Questions