Btukfyl

I have three tables a, b and c.
Table a is related with table b through column key.
table b is related with table c through columns word, sense and speech. In addition table c holds column id.

Now some rows in a.word have no matching value with b.word, based on that
I want to inner join tables on condition if a.word = b.word then join, otherwise compare only a.end_key = b.key.

As a result I want to have table in form of a with extra columns of start_id and end_id from c matching with key_start and key_end.

I tried following sql command with python:

CREATE TABLE relations 

              AS

              SELECT * FROM 

                  c

              INNER JOIN 

                  a

              INNER JOIN

                  b

              ON 

                  a.end_key =  b.key

              AND

                  a.start_key = b.key

              AND

                  b.word = c.word

              AND

                  b.speech = c.speech

              AND

                  b.sense = c.sense

              OR

                  a.word  = b.word

a:

+-----------+---------+------+-----------+

| key_start | key_end | word | relation  |

+-----------+---------+------+-----------+

| k5        | k1      | tree | h         |

| k7        | k2      | car  | m         |

| k200      | k3      | bad  | ho        |

+-----------+---------+------+-----------+

b:

+-----+------+--------+-------+

| key | word | speech | sense |

+-----+------+--------+-------+

| k5  | sky  | a      |     1 |

| k2  | car  | a      |     1 |

| k3  | bad  | n      |     2 |

+-----+------+--------+-------+

c:

+----+---------+--------+-------+

| id |  word   | speech | sense |

+----+---------+--------+-------+

|  0 | light   | a      |     1 |

|  0 | dark    | b      |     3 |

|  1 | neutral | a      |     2 |

+----+---------+--------+-------+

Edit for clarification:

The values of tables a, b and c hold hundreds thousands lines, so there are matching values in the tables. Table a is related to table b with end_key ~ key and start_key~key relation. Table b is related to c through word sense and speech, there are values which match in each of these columns.

The desired table is in form

start_id|key_start|key_end|end_id|relation

Where start_id matches key_start and key_end matches end_id.

EDIT new answer
The problem with the proposed query lies in the use of AND's and OR's (and likely missing (...)). This statement

a.word = b.word then join, otherwise compare only a.end_key = b.key.

would translate to:

AND (a.word= b.word OR a.end_key = b.key).

Maybe try it like this:

         ON 

              b.word = c.word

          AND

              b.speech = c.speech

          AND

              b.sense = c.sense

          AND 

              (a.word  = b.word OR a.end_key = b.key)

It would be a good idea to test in a sqlite manager (eg command line sqlite3, DB Browser for sqlite) before you try it in python; troubleshooting is much easier. And of course test the SELECT before you implement it in a CREATE TABLE.

You could clarify your question by showing the desired columns and result in relations table that this sample data would create (there is nothing between b and c that would match on word, speech, sense). Also the description of the relationship between a and b is confusing. In the first paragraph it says Table a is related with table b through column key. Should key be word?