asked on

SQL - INNER Join multiable feilds

Please look at the attachment.
I have a view setup and need to pull the CCFullName in the fields highlighted and not the ID numbers. I can get this to work for one field. But can get it to work for the others.
Its not showing in my attachment but ttContact is joined to tjprojects to one field that is giving me the Full Name. I need that to work for all of these fields. I dont know how to write the SQL very well I mainly just click on the areas I need and let it build the statement for me.
SQL-INNERJOIN.docx

Patrick Matthews

Try this:

SELECT TOP 100 PERCENT p.ProjectID AS QuoteNum, p.JobNum AS JNum, 
    p.JobName AS JName, p.JobNumName, s.StatusDesc, p.AwardedDate, 
    p.EstNum, p.PCICodeID, cEstimator.CCFUllName AS Estimator, 
    p.SalesID, cEngineer.CCFullName AS Engineer, 
    cDetailer.CCFullName AS Detailer, 
    cProjectMgr.CCFullName AS ProjectMgr, 
    cErectors.CCFullName AS Erectors, p.ReqDelDate, p.PreWage, 
    p.TaxExempt, p.QuoteTotal
FROM dbo.tjProjectsCont pc INNER JOIN
    dbo.tjProjects p ON p.ProjectID = pc.ProjectID INNER JOIN
    dbo.tcStatus s ON p.StatusID = s.StatusID LEFT JOIN
    dbo.ttContacts cEstimator ON p.EstimatorID = cEstimator.CCID LEFT JOIN
    dbo.ttContacts cEngineer ON p.EngineerID = cEngineer.CCID LEFT JOIN
    dbo.ttContacts cDetailer ON p.DetailerID = cDetailer.CCID LEFT JOIN
    dbo.ttContacts cProjectMgr ON p.ProjectMgrID = cProjectMgr.CCID LEFT JOIN
    dbo.ttContacts cErectors ON p.ErectorsID = cErectors.CCID LEFT JOIN
WHERE (s.ActiveJob = 1) AND (pc.PrjContTypeID = 1) AND 
    (p.ProjectID <> 2165)
ORDER BY p.JobName

Open in new window

Note that since you included dbo.tjProjectsCont in your WHERE clause, you are effectively forcing your original query to act as if that table is in an INNER JOIN, so I just made it that way when I reworked it.

scottwoltjer

ASKER

Getting this error when I copy and run what you sent me.
Thank for helping me.

Msg 156, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'WHERE'.

TempDBA

SELECT p.ProjectID AS QuoteNum,
p.JobNum AS JNum,
p.JobName AS JName,
p.JobNumName,
s.StatusDesc,
p.AwardedDate,
p.EstNum,
p.PCICodeID,
cEstimator.CCFUllName AS Estimator,
p.SalesID,
cEngineer.CCFullName AS Engineer,
cDetailer.CCFullName AS Detailer,
cProjectMgr.CCFullName AS ProjectMgr,
cErectors.CCFullName AS Erectors,
p.ReqDelDate,
p.PreWage,
p.TaxExempt,
p.QuoteTotal
FROM dbo.tjProjectsCont pc
INNER JOIN dbo.tjProjects p
ON p.ProjectID = pc.ProjectID
INNER JOIN dbo.tcStatus s
ON p.StatusID = s.StatusID
LEFT JOIN dbo.ttContacts cEstimator
ON p.EstimatorID = cEstimator.CCID
LEFT JOIN dbo.ttContacts cEngineer
ON p.EngineerID = cEngineer.CCID
LEFT JOIN dbo.ttContacts cDetailer
ON p.DetailerID = cDetailer.CCID
LEFT JOIN dbo.ttContacts cProjectMgr
ON p.ProjectMgrID = cProjectMgr.CCID
LEFT JOIN dbo.ttContacts cErectors
ON p.ErectorsID = cErectors.CCID
WHERE (s.ActiveJob = 1)
AND (pc.PrjContTypeID = 1)
AND (p.ProjectID <> 2165)
ORDER BY
p.JobName

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

scottwoltjer

ASKER

Sorry it took so long to get back to this. But that worked great. Thanks very much.