0

one more problem. I need your help.

Make a list of medications that have been entered as the same (identical_with) but differ in their association with the disease.

same medicationsidentical_with

different associationassociation

I don't know how to do that.

The result should be in that case:

enter image description hereresult

user2379123
  • 85
  • 3
  • 14

2 Answers2

1

To solve your problem, you need to use twice the table association. Following code should be OK:

select
    i.Name_1, i.Name_2
from
    association a
inner join
    identical_with i
    on i.Name_1 = a.Name
inner join
    association a2
    on i.Name_2 = a2.Name
where
    a2.Fachname <> a1.Fachname
Cédric L. Charlier
  • 777
  • 4
  • 13
0

This is a bit long for a comment, although the answer is essentially "you can't do this in MySQL".

The support you are looking for is for hierarchical or recursive queries. Almost every databases except MySQL has built-in support for these types of queries. This leaves you with essentially four choices:

  • Switch to using a database that has such support. Among free databases, these include Postgres, SQL Server Express, and Oracle Express.
  • If you limit the depth of equivalence, you can use repeated self joins.
  • You can do this with a while loop in a stored procedure. However, that is not a single SQL statement.
  • Use a nested set model
  • Use a method where you store the full path.

Unfortunately, the last two methods require triggers to maintain the data structure of inserts, updates, and deletes.

Community
  • 1
  • 1
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks for your answer, Gordon. But if you look above, you'll see a solution which works fine. Cedric didn't use a nested set model or repeated self joins. All you need to do the task was to use two inner joins and a 2nd table. Maybe you could show me your solution, it might be helpful. – user2379123 Jan 23 '16 at 17:52
  • @user2379123 . . . That works one layer deep. It does not work for traversing the identical links more than one level. – Gordon Linoff Jan 25 '16 at 01:49