Usher Usher - 11 days ago 8
SQL Question

Trying to join two queries and retrieve a column

I am trying to join two queries

Query 1:

select ProposalTypeID
from tblAssessment
where AssessmentID = AssessmentID


Query 2:

select AssessmentID
from tblAssessmentVersion
where AssessmentVersionID = 3


Thanks for the guidance but I was trying to add that query in stored procedure to retrieve
proposalTypeID
and use in my stored procedure but it complains

SELECT
AssessmentEcosystemCreditID, AssessmentVersionID,
dbo.tblAssessmentEcosystemCredit.ManagementZoneID,
(SELECT ProposalTypeID
FROM tblAssessment
WHERE AssessmentID = (SELECT AssessmentID
FROM tblAssessmentVersion
WHERE AssessmentVersionID = dbo.tblAssessmentEcosystemCredit.AssessmentVersionID)) AS ProposalTypeID,
CASE
WHEN SiteValueCurrentScore <= 17 AND (dbo.tblVegetationZone.EECID > 0 ) AND (ProposalTypeID=699)
THEN 0
ELSE NumberOfCredits
END AS NumberOfCreditst
FROM
dbo.tblAssessmentEcosystemCredit
INNER JOIN
dbo.tblVegetationType ON dbo.tblAssessmentEcosystemCredit.VegTypeID = dbo.tblVegetationType.VegTypeID
INNER JOIN
dbo.tblManagementZone ON dbo.tblAssessmentEcosystemCredit.ManagementZoneID = dbo.tblManagementZone.ManagementZoneID
INNER JOIN
dbo.tblVegetationZone ON dbo.tblVegetationZone.VegetationZoneID = dbo.tblManagementZone.VegetationZoneID
WHERE
dbo.tblAssessmentEcosystemCredit.AssessmentVersionID = @AssessmentVersionID

Answer

so?

select ProposalTypeID 
from tblAssessment 
where AssessmentID in 
  (select AssessmentID 
   from tblAssessmentVersion 
   where AssessmentVersionID=3)