Joining Multiple Tables with different structures?

I have a dataset in SQL with some tables which I need to join them. There is a main table called ‘main_table’ that consists two parameters (id and name) and one of them is the key ‘id’. main_table, TableA, TableB almost have similar structures, but TableC is a bit different as follows:

     main_table:
     id, name,
             01, bb,
             02, cc,
             03, dd,
             04, ff,
             05, gg,

    TableA:
             id, val,
             01, ab,
             03, ac,
             05, ad,
             
    TableB:
             id, val,
             01, ba,
             02, bc,
             04, bd,

    TableC:
             id, val,index
             01, cc, 1
             01, cdf,2
             01, cba,3
             03, ggg,1
             03, dfg,2
             

I could join the first three tables with the following query:

    SELECT main_table.[id],main_table.[name]
           ,TableA.val,TableB.val
     FROM main_table LEFT JOIN TableA ON TableA.id = main_table.id
                     LEFT JOIN TableB ON TableB.id = main_table.id

Which gives me the below table:

     result_table:
     id, name, val_TA, val_TB
             01, bb  , ab  , ba
             02, cc  , NULL, bc
             03, dd  , ac  , NULL
             04, ff  , NULL, bd
             05, gg  , ad  , NULL
     

But, when I want to ‘LEFT JOIN’ the third table (TableC) in this way, it gives me redundant rows, since there are multiple similar ids in TAbleC. I need a query that handles this case and gives me one row for each id when I join them, something like this:

     My_desired_table:
             id, name, val_TA, val_TB,val_TC_index1,val_TC_index2,Val_TC_index3
             01, bb  ,   ab  ,    ba,      cc      ,    cdf      ,     cba
             02, cc  ,   NULL,    bc,      NULL    ,    NULL     ,     NULL
             03, dd  ,   ac  ,  NULL,      ggg     ,    dfg      ,     NULL
             04, ff  ,   NULL,    bd,      NULL    ,    NULL     ,     NULL
             05, gg  ,   ad  ,  NULL,      NULL    ,    NULL     ,     NULL

Answer

There are a couple of ways to achieve this, but perhaps the simplest is as Dale K suggested to PIVOT on tablec before joining to it. To give you an idea how this would work, something along the lines of:

declare @main_table table (id varchar(5), name varchar(5));
declare @tablea table(id varchar(5), val varchar(5));
declare @tableb table(id varchar(5), val varchar(5));
declare @tablec table(id varchar(5), val varchar(5), idx int);

INSERT INTO @main_table
VALUES
('01','bb'),
('02','cc'),
('03','dd'),
('04','ff'),
('05','gg');

INSERT INTO @tablea
VALUES
('01','ab'),
('03','ac'),
('05','ad');

INSERT INTO @tableb
VALUES
('01','ba'),
('02','bc'),
('04','bd');

INSERT INTO @tablec
VALUES
('01','cc',1),
('01','cdf',2),
('01','cba',3),
('03','ggg',1),
('03','dfg',2);


with cte as (
    SELECT id, [1], [2], [3], [4]
    FROM
    @tablec c
    PIVOT
    (
        min(val)
        FOR idx
        IN ([1], [2], [3], [4])
    ) pvt
)
SELECT m.id, m.name, a.val AS val_TA, b.val AS val_TB, 
c.[1] AS val_TC_index1, c.[2] AS val_TC_index2, 
c.[3] AS val_TC_index3, c.[4] AS val_TC_index4
FROM @main_table m
LEFT JOIN @tablea a on a.id = m.id
LEFT JOIN @tableb b on b.id = m.id
LEFT JOIN cte c on c.id = m.id;

I have given a maximum of 4 index columns, but it is easy to see how to extend. If you need this to be open ended, then you will need to convert this into dynamic SQL.

Please note (because you are relatively new) that I have done the hard work for you (providing statements to create the tables and insert the data). In the future when asking such questions, please either do something similar or use db fiddle. You will get many more people willing to help you, if they don’t have to do so much typing. Please remember that people are giving you their time for free.