Basic JOIN Question

Question

Hi,

Good morning. I am trying to join 2 sub-selects with table aliases but keep getting errors at the GROUP BY part of the code. I have slightly redacted the inner workings of the sub-selects.

Msg 156, Level 15, State 1, Line 63
Incorrect syntax near the keyword 'GROUP'.

I am trying to JOIN 2 sub selects

ON UsrActivity.UserID = UserAccList.UserID
ON UsrActivity.SiteID = UserAccList.SiteID

but I cannot get it to work. I feel there is an easy answer here but I am confused.

Thanks for any assistance.

Alastair

SELECT
UserAccList.SiteID,
UserAccList.Name,
UserAccList.UserID,
Count(UserAccList.Name) As TotalLogins,
FROM
(
SELECT
Evt1.Name,
Evt1.UserID,
Evt1.SiteID
FROM
dbo.eventlog1 As Evt1
) As UserAccList
INNER JOIN
(
SELECT
Evt2.Name,
Evt2.UserID,
Evt2.SiteID
FROM
dbo.eventlog2 As Evt2
) As UsrActivity
INNER JOIN UserAccList ON UsrActivity.UserID = UserAccList.UserID
INNER JOIN UserAccList ON UsrActivity.SiteID = UserAccList.SiteID
GROUP BY
UserAccList.SiteID,
UserAccList.UserID,
UserAccList.Name
ORDER BY
UserAccList.Name ASC

Answer 1

Hi，@Alastair MacFarlane

Welcome to Microsoft T-SQL Q&A Forum!

Try this：

 ;With cte as  
 (  
  SELECT Name, UserID, SiteID FROM dbo.eventlog1   
  intersect   
  SELECT Name, UserID, SiteID FROM dbo.eventlog2   
 )  
 select * from cte  
 group by  SiteID, UserID, Name  
 order by Name asc  

IF you want to know more about the usage of intersect,you can look this document.

Best regards,
Bert Zhou

---

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.

Answer 2

Apologies I think I see an issue. I have an INNER JOIN between the sub-selects. What is the best way to code this?

Answer 3

If I change it to:

) UsrActivity

ON UsrActivity.UserID = UserAccList.UserID
INNER JOIN UserAccList
ON UsrActivity.SiteID = UserAccList.SiteID

I get:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'UserAccList'.

Answer 4

SELECT SiteID,Name,UserID,Count(*) As TotalLogins
FROM
(
Select
SELECT Name, UserID, SiteID FROM dbo.eventlog1
intersect
SELECT Name, UserID, SiteID FROM dbo.eventlog2
) t

GROUP BY SiteID, UserID, Name
ORDER BY Name