Mysql – Problem in using muliple inner join

MySQL

I have four tables :

table p_d :

id   price
1     12
2     13

table s_d

id   name
1    name1
2    name2

table b_d

id    p_d_id
1       1
2       1

table b_d_s

id     b_d_id    s_d_id
1       1         1
2       1         2

Some info about tables :

for table b_d

column p_d_id reference p_id (id)

for table b_d_s

column b_d_id references b_d(id) , column s_d_id references s_d(id)

Now problem is that, i am unable to link all tables together using inner join .

What i need from table b_d_s:

p_d.price where s_d_id = 1

I am not able to join table b_d_s and table p_d because there is not any common column between them.

result might be 

price 
 12

I also tried it at my end , but could not join all tables together. i used inner join .

Best Answer

Try this (in an INNER JOIN you don't necessary have to reference previous table):

select p_d.price
 from b_d_s inner join b_d  on b_d_s.b_d_id = b_d.id
            inner join s_d  on b_d_s.s_d_id = s_d.id
            inner join p_d  on b_d.p_d_id   = p_d.id
 where s_d.id = 1
Related Question