how to join 3 relational tables

how to join 3 relational tables with the structure:

t1 | id
t2 | id | rating
t3 | source_id | relation
开发者_运维百科

t3 stores the data of a field which t1 and t2 uses both. so source_id field can be t1's id or t2's id.

input  : t1 id
output : t2 rating

an example:

**t1**

id |
---------
42 |

**t2**

id | rating  
-------------
37 | 9.2

**t3**

id | source_id
--------------
42 | 1
37 | 1
26 | 2
23 | 1

what i want is to get 9.2 output with 42 input.

can you do that in one sql query?

You "relational tables" are a disaster; the SQL code (SQL is designed to navigate Relational Databases) required will be a further disater, never complete or stable. Coding issues are consequential, second order problems, which identify errors in the first order.

First, normalise and model your data accurately for the Relational DBMS that you have placed it in; and so that you can use the SQL Relational Language. Then the code will be simple, easy, straight-forward and complete.

If you want me to model the data, fine. But I need more information than cryptic Id columns; I need names of entities and columns, what they mean, how they are actually related to each other.

Responses to Comments

Yes, you certainly can have a column that is a Foreign Key referencing more than one parent table. But you cannot (in the Relational Database) have a column that is a either one or another parent table: that will lead to orphans, spinsters, and all kinds of easily avoidable coding horrors.

This query will give you the desired output on your data, though don't think it's correct in your model:

SELECT  t2.rating
FROM    t1
JOIN    t3 t31
ON      t31.id = t1.id
JOIN    t3 t32
ON      t32.source_id = t31.source_id
JOIN    t2
ON      t2.id = t32.id
WHERE   t1.id = 42

i found the solution, what do you think?

SELECT t2.rating
FROM t3 AS t3
inner join t1 AS t1 on t1.id = t3.id
inner join (SELECT source_id, rating
FROM t3 AS t32
inner join t2 AS review on t2.id = t32.id) as second on second.source_id = t3.source_id
where t1.id = 42 limit 0,1