• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 603
  • Last Modified:

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
0
scottwoltjer
Asked:
scottwoltjer
  • 2
  • 2
1 Solution
 
Patrick MatthewsCommented:
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.
0
 
scottwoltjerAuthor Commented:
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'.
0
 
TempDBACommented:
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
0
 
Patrick MatthewsCommented:
Sorry, left in a bit I was supposed to have deleted.

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
WHERE (s.ActiveJob = 1) AND (pc.PrjContTypeID = 1) AND 
    (p.ProjectID <> 2165)
ORDER BY p.JobName

Open in new window

0
 
scottwoltjerAuthor Commented:
Sorry it took so long to get back to this. But that worked great. Thanks very much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now