Solved

SQL Alias Use

Posted on 2015-01-22
4
99 Views
Last Modified: 2015-01-22
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.
0
Comment
Question by:cbridgman
4 Comments
 
LVL 33

Assisted Solution

by:Mike Eghtebas
Mike Eghtebas earned 250 total points
ID: 40565298
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.[People ID] = jobs.[Updater ID]
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 250 total points
ID: 40565306
Try putting name and phone in hard brackets, e.g. Updated.[name]
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40565312
He's got it.  Same for the other people join too (please, NO pts for me):

left outer join
      people as updater
 on
     updater.[Updater ID] = jobs.updaterid
 left outer join
     people as creator
 on
     creator.[Creator ID] = jobs.creatorid
0
 

Author Comment

by:cbridgman
ID: 40565396
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Webservices in T-SQL 3 30
SQL Server 2012 r2 - Varible Table 3 20
Sql Server group by 10 25
Insert query into temp tables using Coldfusion 3 13
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

813 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now