CODEWITHSUNDEEP
sqlinner-join
Riney Ahuja
Riney Ahuja

Reputation: 7

Inner join more than 2 tables in a single database

i want to innerjoin more than 3 tables in one select query in one database all of them are having rm_id as a common column but i want to display the rows of all tables in one sql query is it possible if it is then i would hearly request u to provide some code

select * from 
        bk_det inner join
        bk_rep inner join
        bk_sec inner join
        mut_det inner join
        rm_det inner join
        soil_det 
on
          bk_det.rm_id =   bk_rep.rm_id =  bk_sec.rm_id =  mut_det.rm_id = rm_det.rm_id = soil_det.rm_id

Upvotes: 0

Views: 8350

Answers (2)

Taryn
Taryn

Reputation: 247880

You are missing the ON clause for each join:

select * 
from bk_det 
inner join bk_rep 
  on bk_det.rm_id =  bk_rep.rm_id
inner join bk_sec 
  on bk_rep.rm_id = bk_sec.rm_id
inner join mut_det 
  on bk_sec.rm_id =  mut_det.rm_id
inner join rm_det 
  on mut_det.rm_id = rm_det.rm_id
inner join soil_det 
    on rm_det.rm_id = soil_det.rm_id

Note: you will have to check the join condition column names since I do not know your table structure

If you need help with learning join syntax, here is a great visual explanation of joins.

Since you are using an INNER JOIN this will return records if the rm_id exists in each table.

You might need to use a LEFT JOIN:

select * 
from bk_det 
left join bk_rep 
  on bk_det.rm_id =  bk_rep.rm_id
left join bk_sec 
  on bk_rep.rm_id = bk_sec.rm_id
left join mut_det 
  on bk_sec.rm_id =  mut_det.rm_id
left join rm_det 
  on mut_det.rm_id = rm_det.rm_id
left join soil_det 
    on rm_det.rm_id = soil_det.rm_id

Upvotes: 2

Joe
Joe

Reputation: 42666

You need an on clause for each inner join. e.g. 

select * from a 
inner join b on a.id = b.id 
inner join c on b.id = c.id

Upvotes: 5

Related Questions