How to incorporate a join with current sql query syntax

I have a sql query that works well. I need to add an additional field form tblIR called review (table) BUT it does not always have a record for the field used as the key.

When I add "and f..gateid = a.gateId to current sql query, I lose rows, since it is only bringing back matching rows.

Is there a way to code a join for table a (a.gateid) and table f (f.gateid) without having to recode the sql query so if there is no record, the field for "review" is blank. If there is a hit it says "yes:.  

(or do I have to code into joins for the whole sql query?)
sqlquery.docx
mahpogAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
_agx_Connect With a Mentor Commented:
Personally I would use ansi-92+ syntax and add an OUTER JOIN on "gateId".  Then use a CASE to display 'Yes' when the "gateId" has a match, or "No" otherwise.

SELECT
e.Acronym as Bus
, d.Acronym as BusArea
, a.Gateid
, a.ModifiedDate
, a.Modid
, a.IRID
, a.plandate
, a.gateactualdate
, a.PlanDateChgReason,
, a.PlanDateChgReasonModifiedDate
, CASE WHEN f.gateid IS NOT NULL THEN 'Yes' ELSE 'No' END AS Review
 
FROM tblgate a
		INNER JOIN tblMod b ON a.ModID = b.ModID
		INNER JOIN tblContract c ON b.contractID = c.ContractID
		INNER JOIN tblBusinessArea d ON c.BusinessAreaID = d.BusinessAreaID
		INNER JOIN tblBusiness e ON d.BusinessID = e.BusinessID
		LEFT JOIN review f ON f.gateid = a.gateId
WHERE e.Acronym = 'XXXXXX'
ORDER BY  a.GateId

Open in new window

0
 
mahpogAuthor Commented:
Right on point. I guess I should just use new syntax. Thanks!
0
All Courses

From novice to tech pro — start learning today.