Solved

How to incorporate a join with current sql query syntax

Posted on 2016-11-15
2
33 Views
Last Modified: 2016-12-28
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
0
Comment
Question by:mahpog
2 Comments
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 41888890
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
 

Author Closing Comment

by:mahpog
ID: 41889767
Right on point. I guess I should just use new syntax. Thanks!
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question