SQL Innerjoin

Handian Sudianto 4,511 Reputation points
2023-12-04T06:04:51.34+00:00

Hello,

I have an query like this

Select nodeid, sum(In_TotalBytes/1000000000) as Down , sum(Out_TotalBytes/1000000000) as Up from InterfaceTrafficByDays where nodeid in (select nodeid from nodes where Isserver=1) and month(datetime)=month(getdate())-0 and year (datetime)=year(getdate()) or (year(datetime) = year(getdate()) - 0 and month(datetime) = 12 and month(getdate()) = 1) group by nodeid

User's image

And for the result i want to inner join to other table to get the details by change the query to

Select nodeid, sum(In_TotalBytes/1000000000) as Down , sum(Out_TotalBytes/1000000000) as Up from InterfaceTrafficByDays where nodeid in (select nodeid from nodes where Isserver=1) and month(datetime)=month(getdate())-0 and year (datetime)=year(getdate()) or (year(datetime) = year(getdate()) - 0 and month(datetime) = 12 and month(getdate()) = 1) group by nodeid A inner join nodes B on A.nodeid=B.nodeid

but i got an error "Msg 156, Level 15, State 1, Line 6Incorrect syntax near the keyword 'inner'."

Anyone know what should i do?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,351 questions
0 comments No comments
{count} votes

Accepted answer
  1. LiHongMSFT-4306 25,651 Reputation points
    2023-12-04T06:25:28.77+00:00

    Hi @Handian Sudianto

    Try this:

    ;with cte as
    (
    Select nodeid
    , sum(In_TotalBytes/1000000000) as Down 
    , sum(Out_TotalBytes/1000000000) as Up 
    from InterfaceTrafficByDays 
    where nodeid in (select nodeid from nodes where Isserver=1) 
    and month(datetime)=month(getdate())-0 
    and year (datetime)=year(getdate()) 
    or (year(datetime) = year(getdate()) - 0 
    and month(datetime) = 12 
    and month(getdate()) = 1) 
    group by nodeid 
    )
    select A.node, A.Down, A.Up, B.*--columns you want 
    from cte A inner join nodes B on A.nodeid=B.nodeid
    

    Best regards,

    Cosmog Hong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


0 additional answers

Sort by: Most helpful