How in inner join few table consecutively in single sql

Question

i am currently have few 5 table , and i wan to inner join table 1 to table 2, and then table to 2 to table 3, and so on....

i have tried this with few separated inner join, but how to get the same result with only 1 sql

SELECT table1.CW_S_EVT,table2.CW_S_TYP, table2.CW_S_SERVER
FROM table1 INNER JOIN table2
ON table1.CW_S_EVT = table2.CW_S_EVT;

SELECT table2.CW_S_EVT,table2.CW_S_TYP, table3.CW_S_TPL
FROM table2 INNER JOIN table3
ON table2 .CW_S_TYP = table3.CW_S_TYP

SELECT  table3.CW_S_TPL, table4.CW_L_TPL,table4.CW_CONTENT
FROM table3 INNER JOIN table4
ON table3.CW_S_TPL = table4.CW_S_TPL

SELECT table1.CW_S_EVT,table5.CW_S_VAR, table5.CW_L_VAR
FROM table1 INNER JOIN table5
ON table1.CW_S_EVT = table5.CW_S_EVT;

This is the code i found on internet but it does not match my case.

SELECT * 
FROM table1 
INNER JOIN table2
      ON table1.primaryKey=table2.table1Id
INNER JOIN table3
      ON table1.primaryKey=table3.table1Id

The syntax of the multi-table join is correct. Since we have absolutely no clue what you are trying to achieve, we cannot help you any further, until you explain what's wrong with that query! You probably yave to share some sample data with us. — Shadow, Oct 14 '19 at 03:22
Hi, thank you for yr reply, the multi table join is joining table 1 and table 2, table 1 and table 3 but what i need is joining table 1 to table 2, and then table to 2 to table 3, and so on. — Joanlum, Oct 14 '19 at 03:28
i tough is the same question here : https://stackoverflow.com/questions/11321354/join-3-tables-in-sqlite-database — Pasha, Oct 14 '19 at 03:29
Why don't you do that then, instead of joining table3 on table1? — Shadow, Oct 14 '19 at 05:49

score 1 · Answer 1 · answered Oct 14 '19 at 03:30

What you need is a left join your tables on your criteria. Since this will result to null values, adding coalesce function will be able to help us give those values that matches your criteria.

Additional distinct keyword if its needed.

SELECT distinct coalesce(table1.CW_S_EVT, table2.CW_S_EVT, table3.CW_S_TPL) 
    , coalesce(table2.CW_S_TYP, table4.CW_L_TPL, table5.CW_S_VAR)
    , coalesce(table2.CW_S_SERVER, table3.CW_S_TPL, table4.CW_CONTENT, table5.CW_L_VAR)
FROM table1 
LEFT JOIN table2 ON table1.CW_S_EVT = table2.CW_S_EVT;
LEFT JOIN table3 ON table2.CW_S_TYP = table3.CW_S_TYP
LEFT JOIN table4 ON table3.CW_S_TPL = table4.CW_S_TPL
LEFT JOIN table5 ON table1.CW_S_EVT = table5.CW_S_EVT;

score 0 · Answer 2 · answered Oct 14 '19 at 03:57

Try this

SELECT
    table1.CW_S_EVT as t1_CW_S_EVT, 
    table2.CW_S_TYP as t2_CW_S_TY, 
    table2.CW_S_SERVE as t2_CW_S_SERVE, 
    table2.CW_S_EVT as t2_CW_S_EVT, 
    table3.CW_S_TPL as t3_CW_S_TPL, 
    table4.CW_L_TPL as t4_CW_L_TPL, 
    table4.CW_CONTENT as t4_CW_CONTENT,
    table5.CW_S_VAR as t5_CW_S_VAR, 
    table5.CW_L_VAR as t5_CW_L_VAR

FROM
    table1, table2, table3, table4, table5 
where  
    table1.CW_S_EVT = table2.CW_S_EVT AND 
    table2 .CW_S_TYP = table3.CW_S_TYP AND 
    table3.CW_S_TPL = table4.CW_S_TPL AND 
    table1.CW_S_EVT = table5.CW_S_EVT;

score 0 · Answer 3 · answered Oct 14 '19 at 05:15

SELECT table1.CW_S_EVT,table3.CW_S_TYP,table4.CW_S_TPL, table5.CW_S_VAR
FROM table1
INNER JOIN table2 ON table1.CW_S_EVT = table2.CW_S_EVT 
    INNER JOIN table3 ON table2.CW_S_TYP = table3.CW_S_TYP 
        INNER JOIN table4 ON table3.CW_S_TPL = table4.CW_S_TPL 
            INNER JOIN table5 ON table1.CW_S_EVT = table5.CW_S_EVT

Thanks for all the kind answer, but i found this working at last, juz record here maybe for future use.^^

Feel free to accept your own answer – Strawberry Oct 14 '19 at 05:50 — Strawberry, Oct 14 '19 at 05:50

How in inner join few table consecutively in single sql

3 Answers3

Try this