I am a novice SQL guy so you all take it easy on me.

I am trying to run a query and one of the columns of my has values that is
referenced in another table, i.e. for EmployedStatus I have a bunch a
numbers from aother table. I go to that table to see what the numbers mean
and there you go. What I would like to do is, say I am working with person
profile table and instead of showing the column "EmployedStatus" as a 63
(for example) for a record, I would like it to show what that 63 means from
the "desription" column of the table that the 63 is referenced to. I hope
that make sense and I would appreciate any help anyone can stand to stomach!

Thanks,
Aaron

It's ok Aaron, we all started out somewhere.

For reference, look in Books Online for the topic JOIN TABLES -its a complex set of topics and takes most of us a lot of practice to get it right.

Now about your problem. I'll create an example. (You can copy and paste this code into Query Analyzer and run it.)

SET NOCOUNT ON

CREATE TABLE PersonProfile
( EmployeeID int IDENTITY,
LastName varchar(20),
FirstName varchar(20),
EmployedStatus int
)

CREATE TABLE TableB
( Status int,
Description varchar(50)
)

INSERT INTO PersonProfile VALUES ( 'Smith', 'John', 63 )
INSERT INTO TableB VALUES ( 63, 'On Leave' )

--Join the two tables
SELECT
p.EmployeeID,
p.LastName,
p.FirstName,
CurrentStatus = b.Description
FROM PersonProfile p
JOIN TableB b
ON p.EmployedStatus = b.Status

--Clean up
DROP TABLE PersonProfile
DROP TABLE TableB


--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf


"Aaron" <support (AT) pinetreeit (DOT) com> wrote

Thanks Arnie for the info, worked great.

If I could ask one more; How would I modify this code to to be able to
reference more than one field that uses the same table? For example to
continue with our example, I have a an EmployedStatusID that is referenced
by a number in an ID Table and it corresponding Description of Full-Time,
Part-Tim, etc. I also have a StudentStatusID that is referenced by a number
in the same ID table with it's corresponding Description as Full-Time,
Half-Time, etc.. I tried to use the AND along with the Join and when I
added more than one, no data was displayed. If I run separate queries on
both, they show up correctly. Any suggestions?

Thanks again,

Aaron
"Arnie Rowland" <arnie (AT) 1568 (DOT) com> wrote

It's ok Aaron, we all started out somewhere.

For reference, look in Books Online for the topic JOIN TABLES -its a complex
set of topics and takes most of us a lot of practice to get it right.

Now about your problem. I'll create an example. (You can copy and paste this
code into Query Analyzer and run it.)

SET NOCOUNT ON

CREATE TABLE PersonProfile
( EmployeeID int IDENTITY,
LastName varchar(20),
FirstName varchar(20),
EmployedStatus int
)

CREATE TABLE TableB
( Status int,
Description varchar(50)
)

INSERT INTO PersonProfile VALUES ( 'Smith', 'John', 63 )
INSERT INTO TableB VALUES ( 63, 'On Leave' )

--Join the two tables
SELECT
p.EmployeeID,
p.LastName,
p.FirstName,
CurrentStatus = b.Description
FROM PersonProfile p
JOIN TableB b
ON p.EmployedStatus = b.Status

--Clean up
DROP TABLE PersonProfile
DROP TABLE TableB


--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf


"Aaron" <support (AT) pinetreeit (DOT) com> wrote

Aaron,

As you noticed, trying to retrieve fields from a JOIN table where the JOIN criteria is different doesn't work.

So there are a couple of different ways to make this work. One involves using a JOIN with a second virtual copy of the table, and the other involves using a sub-SELECT.

First, the JOIN using a second copy of the same table, and then the sub-select.


SET NOCOUNT ON

CREATE TABLE PersonProfile
( EmployeeID int IDENTITY,
LastName varchar(20),
FirstName varchar(20),
EmployedStatus int,
StudentStatus int
)

CREATE TABLE Status
( StatusID int,
Description varchar(50)
)

INSERT INTO PersonProfile VALUES ( 'Smith', 'John', 63, 23 )
INSERT INTO Status VALUES ( 63, 'On Leave' )
INSERT INTO Status VALUES ( 21, 'Full Time' )
INSERT INTO Status VALUES ( 21, 'Half Time' )
INSERT INTO Status VALUES ( 23, 'Part Time' )

--Join the two tables
SELECT
p.EmployeeID,
p.LastName,
p.FirstName,
CurrentStatus = s1.Description,
StudentStatus = s2.Description
FROM PersonProfile p
JOIN Status s1
ON p.EmployedStatus = s1.StatusID
JOIN Status s2
ON p.StudentStatus = s2.StatusID


--Use a Sub-SELECT
SELECT
p.EmployeeID,
p.LastName,
p.FirstName,
CurrentStatus = ( SELECT Description
FROM Status
WHERE StatusID = p.EmployedStatus
),
StudentStatus = ( SELECT Description
FROM Status
WHERE StatusID = p.StudentStatus
)
FROM PersonProfile p

--Clean up
DROP TABLE PersonProfile
DROP TABLE Status


--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf


"Aaron" <support (AT) pinetreeit (DOT) com> wrote