cbridgman
asked on
SQL Alias Use
Using SQL Server, I'm attempting to put a query together that gets most of it's data from a main table and some related data from another table. The main table has two fields in it, both of which have related information in the other table. By way of example, the main table, we will call it the jobs table, has two fields in it, one for the id of the person who created the record and the other for the id person who updated the record. The other table, call it the people table, contains the names of all people and their phone numbers.
In my result set, I want a list of every job in the jobs table together with the ids, names, and phones of their creators and updaters. The tables look kind of like this:
Jobs Table
---------------
Job ID
Job Name
Creator ID
Creator Name
Creator Phone
Updater ID
Updater Name
Updater Phone
People Table
------------------
People ID
Name
Phone
The Query that I've put together and that isn't working is as follows:
select
jobs.jobid, jobs.jobname, jobs.creatorid, jobs.updaterid,
updater.name as updater_name, updater.phone as updater_phone,
creator.name as creator_name, creator.phone as creator_phone
from
jobs
left outer join
people as updater
on
updater.peopleid = jobs.updaterid
left outer join
people as creator
on
creator.peopleid = jobs.creatorid
SQL complains that the name and phone columns in the select statement are invalid.
Any help would be appreciated.
In my result set, I want a list of every job in the jobs table together with the ids, names, and phones of their creators and updaters. The tables look kind of like this:
Jobs Table
---------------
Job ID
Job Name
Creator ID
Creator Name
Creator Phone
Updater ID
Updater Name
Updater Phone
People Table
------------------
People ID
Name
Phone
The Query that I've put together and that isn't working is as follows:
select
jobs.jobid, jobs.jobname, jobs.creatorid, jobs.updaterid,
updater.name as updater_name, updater.phone as updater_phone,
creator.name as creator_name, creator.phone as creator_phone
from
jobs
left outer join
people as updater
on
updater.peopleid = jobs.updaterid
left outer join
people as creator
on
creator.peopleid = jobs.creatorid
SQL complains that the name and phone columns in the select statement are invalid.
Any help would be appreciated.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I actually figured out what the problem was. The phone number attribute in the people table is non-persistent. Therefore, SQL Server complained. I drew the relationship to the correct table - the PHONE table - and all was good. My SQL Statement would have worked if I used the correct table names.
Thanks for your assistance.
Thanks for your assistance.
left outer join
people as updater
on
updater.[Updater ID] = jobs.updaterid
left outer join
people as creator
on
creator.[Creator ID] = jobs.creatorid