Add Two Columns Form Another Table To An MS SQL Select Satement

How would I go about adding two columns from one table to a select query for another table based on a column value in the table being selected from?

Here is my first attempt at an INNER JOIN which doesn't work, but hopefully my incorrect code shows what I'm trying to accomplish

SELECT CLQM001ID AS ImportID,
DIV AS Division,
DEPT AS Department,
CREATED_DATE AS Date_1,
STAKEHOLDERSUP AS StakeholderSupplier,
INITIATOR,
JOBNUM AS Job,
ITPNUM AS ITPNo,
DESCRIPTION,
PROBLEM AS ProblemDetails,
ROOTCAUSE AS RootCauseDescription,
PRIMARYROOTCAUS AS PrimaryRootCauseType,
SECONDROOTCAUSE AS SeconadryRootCauseType,
Cost AS Cost_1,
DISPOSITIONFINAL AS Disposition,
CORRECTIVEACTION,
VERIFCORECTACT AS CorrectEffective,
NOTES AS VerifyNotes
FROM RB1_CLQM001 AS qm
INNER JOIN ACTIVITIES.CODE ON qm.ACT = ACTIVITIES.ID
INNER JOIN ACTIVITIES.[DESCRIPTION] ON qm.ACT = ACTIVITIES.ID

Open in new window


This way get's me the error " Invalid object name 'ACTIVITIES.CODE'. "

If I remove the dot between the ACTIVITIES table and the CODE column name I get the following error: " The multi-part identifier 'ACTIVITIES.ID' could not be bound."
LVL 2
byt3Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byt3Author Commented:
Okay, so two minutes after I post this I do another google search (I did many already) and I found an INNER JOIN example that makes sense to me and now it is working.

SELECT CLQM001ID AS ImportID,
DIV AS Division,
DEPT AS Department,
CREATED_DATE AS Date_1,
STAKEHOLDERSUP AS StakeholderSupplier,
INITIATOR,
JOBNUM AS Job,
ITPNUM AS ITPNo,
a.CODE AS Activity1,
a.[DESCRIPTION] AS ActivityDescription
qm.[DESCRIPTION],
PROBLEM AS ProblemDetails,
ROOTCAUSE AS RootCauseDescription,
PRIMARYROOTCAUS AS PrimaryRootCauseType,
SECONDROOTCAUSE AS SeconadryRootCauseType,
Cost AS Cost_1,
DISPOSITIONFINAL AS Disposition,
CORRECTIVEACTION,
VERIFCORECTACT AS CorrectEffective,
NOTES AS VerifyNotes
FROM RB1_CLQM001 AS qm
INNER JOIN ACTIVITIES a ON qm.ACT = a.ID

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
byt3Author Commented:
I finally figured it out myself.
0
PortletPaulfreelancerCommented:
congratulations!

useful on joins
http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins
http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

http://en.wikipedia.org/wiki/Join_(SQL)

useful images:
http://www.codeproject.com/KB/database/Visual_SQL_Joins/Visual_SQL_JOINS_orig.jpg
http://upload.wikimedia.org/wikipedia/commons/9/9d/SQL_Joins.svg

SELECT
        CLQM001ID        AS ImportID
      , DIV              AS Division
      , DEPT             AS Department
      , CREATED_DATE     AS Date_1
      , STAKEHOLDERSUP   AS StakeholderSupplier
      , INITIATOR
      , JOBNUM           AS Job
      , ITPNUM           AS ITPNo
      , a.CODE           AS Activity1
      , a.[DESCRIPTION]  AS ActivityDescription
      , qm.[DESCRIPTION]  --                           <<<<<<<< missing comma
      , PROBLEM          AS ProblemDetails
      , ROOTCAUSE        AS RootCauseDescription
      , PRIMARYROOTCAUS  AS PrimaryRootCauseType
      , SECONDROOTCAUSE  AS SeconadryRootCauseType
      , Cost             AS Cost_1
      , DISPOSITIONFINAL AS Disposition
      , CORRECTIVEACTION
      , VERIFCORECTACT   AS CorrectEffective
      , NOTES            AS VerifyNotes
FROM RB1_CLQM001 AS qm
INNER JOIN ACTIVITIES AS a  --             <<<< should use "AS" 
        ON qm.ACT = a.ID

Open in new window

useful in many ways (formatting/checking):
http://www.sql-format.com/
http://poorsql.com/
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.