[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL - INNER Join multiable feilds

Posted on 2012-09-07
5
Medium Priority
?
597 Views
Last Modified: 2012-10-02
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
Comment
Question by:scottwoltjer
  • 2
  • 2
5 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 38377552
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
 

Author Comment

by:scottwoltjer
ID: 38392893
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
 
LVL 25

Expert Comment

by:TempDBA
ID: 38394133
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
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 38394519
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
 

Author Closing Comment

by:scottwoltjer
ID: 38417689
Sorry it took so long to get back to this. But that worked great. Thanks very much.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

830 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