Joining Multiple Tables in SQL

Joining Multiple Tables with different structures?

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.

joining multiple tables in an SQL query

Left join 101... and you really....really need to read up on joins if you plan to work in RDMS.

select
    b.isbn
    ,b.title
    ,b.bookprice
    ,b.stock
    ,a.authorname
    ,o.ordernumber
    ,o.numcopies
    ,o.price
from
    book b
    inner join 
        BookAuthor ba on
        ba.isbn = b.isbn
    inner join
        Author a on
        a.authorid = ba.authorid
    left join
        orderline o on
        o.isbn = b.isbn
    left join
        bookorder bo on
        bo.ordernumber = o.ordernumber
where
    b.isbn = 1491936169

Joining multiple tables ORACLE

This would be equivalent (if we also add the additional criteria you mentioned):

FROM 
    SD 
    
    INNER JOIN STORE as S
    on S.STOREID = SD.STOREID

    INNER JOIN MATERIAL as M
    ON M.MATERIALID = SD.MATERIALID

    LEFT JOIN STOREBIN as SB
    ON SB.STOREBINID = SD.STOREBINID

    LEFT JOIN VENDOR as V
    ON V.VENDORID = SD.VENDORID

    LEFT JOIN MATERIALPRICE as MP
    ON MP.MATERIALID = M.MATERIALID

Joining multiple tables with ValidFrom/ValidTo dates (SCD2)

Would you be interested in using SqlServer's geometry data type to represent time periods? Here I applied it to your example:

WITH c (clientCode, [name], Perd) AS (
    SELECT clientCode, [name],
           Perd=geometry::STGeomFromText('LINESTRING (' + format(startdate,'yyyyMMdd')+' 0, '+
                      format(LEAD(startDate, 1, {d '2099-12-31'}) OVER (
                               PARTITION BY clientCode
                               ORDER BY startDate) , 'yyyyMMdd') +' 0)', 0)
    FROM #Clients),

--- Projects:
     p (projectCode, clientCode, [name], Perd) AS (
    SELECT projectCode, clientCode, [name], 
           Perd=geometry::STGeomFromText('LINESTRING (' + format(startdate,'yyyyMMdd')+' 0, '+
                       format(LEAD(startDate, 1, {d '2099-12-31'}) OVER (
                                PARTITION BY projectCode
                                ORDER BY startDate) , 'yyyyMMdd') +' 0)', 0)
    FROM #Projects)
SELECT c.clientCode, c.[name] AS clientName,
       p.projectCode, p.[name] AS projectName,
       startDate=try_cast(format(c.Perd.STIntersection(p.Perd).STEndPoint().STX ,'########') as date),
       endDate=try_cast(format(c.Perd.STIntersection(p.Perd).STStartPoint().STX, '########') as date)
FROM 
    c
    inner join
    p on
    c.clientCode=p.clientCode AND p.Perd.STIntersection(c.Perd).STLength()>0
order by 1,5

This can be easier to nest as a subquery, and join to another temporal table.

I would imagine that this wouldn't be very fast with very large data-sets, though.

DBT join multiple tables

since clients_last_updated_grouped doesn't have a where condition, it's guaranteed to have all of the year/month combinations found in the other models. This makes it much easier. You can just select from that model and join the other models on year and month:

with
    updated as (select * from {{ ref('clients_last_updated_grouped') }} ),
    deleted as (select * from  ),
    service as (select * from  ),

    joined as (
        select
            updated.year,
            updated.month,
            updated.client_count,
            coalesce(deleted.deleted, 0) as deleted_count,
            coalesce(service.service, 0) as service_count

        from
            updated
            left join deleted on updated.year = deleted.year and updated.month = deleted.month
            left join service on updated.year = service.year and updated.month = service.month
    )
select *
from joined

If your database doesn't support CTEs (with ...), this becomes:

select
    updated.year,
    updated.month,
    updated.client_count,
    coalesce(deleted.deleted, 0) as deleted_count,
    coalesce(service.service, 0) as service_count

from
    {{ ref('clients_last_updated_grouped') }} as updated
    left join {{ ref('clients_deleted_grouped') }} as deleted on updated.year = deleted.year and updated.month = deleted.month
    left join {{ ref('clients_service_grouped') }} as service on updated.year = service.year and updated.month = service.month

If it's not the case that clients_last_updated_grouped has every month/year combination of the other tables, you would need to first construct a "date spine", and then left join all 3 tables to that date spine.

How can I join multiple tables with same primary key to one table with one row per key?

select key1, key2, key3,
       tab1.value as value1,
       tab2.value as value2,
       tab3.value as value3,
       tab4.value as value4,
       tab5.value as value5,
       tab6.value as value6
from tab1 full join
     tab2
     using (key1, key2, key3) full join
     tab3
     using (key1, key2, key3) full join
     tab4
     using (key1, key2, key3) full join
     tab5
     using (key1, key2, key3) full join
     tab6
     using (key1, key2, key3);

The using clause ignores NULLs for a key value if another value is available, so the complex coalesce() expressions are not needed. You can then refer to the key values -- without table aliases -- elsewhere in the query.

---

Related Topics