How can we count records from multiple joins when ID is used multiple times?-CodePudding

I'm trying to get the record count from multiple tables, like this.

Select count(*)
From
(
Select  Hist.Common_Name,
        Veg.ID,
        EDSH.ID
From Hist_Event_View as Hist
Inner Join Vegtables as Veg
ON Hist.Common_Name = Veg.ID
INNER JOIN Final as Final
ON Hist.Common_Name = Final.ID) as Sub

The problem is that ID is being used multiple times, so SQL Server can't resolve which ID is coming from which table in the outer query, I think. How can I handle this issue? Thanks.

CodePudding user response：

Your assumption is correct, the duplicate ID's are the problem. You can handle this by giving the ID's an alias in the subquery:

Select count(*)
From
(
Select  Hist.Common_Name,
        Veg.ID as Veg_ID,
        EDSH.ID as EDSH_ID
From Hist_Event_View as Hist
Inner Join Vegtables as Veg
ON Hist.Common_Name = Veg.ID
INNER JOIN Final as Final
ON Hist.Common_Name = Final.ID) as Sub

CodePudding user response：

I think this could be fixed by using an alias https://www.w3schools.com/sql/sql_alias.asp

But why do you need an outer select? Why not just write:

Select  count(*)
From Hist_Event_View as Hist
Inner Join Vegtables as Veg
ON Hist.Common_Name = Veg.ID
INNER JOIN Final as Final
ON Hist.Common_Name = Final.ID