Query question

[ichervin]

Hi All,

I have a query that looks like this:

select table3.lastname, table1.stationid, table2.entryuserid, table2.updateuserid
from table1 vp, table2 tr, table3 emp
where table3.empid = table2.empid and
table3.positionid = table1.positionid


Table 1 stores stationids
Table 2 stores entryuserids, updateuserids
Table 3 stores lastnames

I need to translate table2.entryuserid and table2.updateuserid to their lastnames from table3. Please help. Thanks.

[nosepicker]

I'm not sure that I see anything wrong with the logic of the query. However, the syntax should be changed to this:

select table3.lastname, table1.stationid, table2.entryuserid, table2.updateuserid
from table1 vp, table2 tr, table3 emp
where emp.empid = tr.empid and
emp.positionid = vp.positionid

Otherwise, I'm not sure what problems you are having. Does it not return the correct results?

[ichervin]

It returns correct results.

Results:

lastname stationid entryuserid
Smith 30 Z000099

updateuserid
Z000077


Instead of displaying the entryuserid and updateuserid I need to find and display the corresponding lastnames from table 3 emp. I am not sure how to do that.

Expected results:

lastname stationid entryuserid
Smith 30 Michaels

updateuserid
Johnson

Hope it make sense.

[nosepicker]

How does table2.entryuserid and table2.updateuserid relate to table3?

Are there entryuserid and updateuserid columns in table3? Does table2.entryuserid = table3.empid? table2.entryuserid = table3.positionid?
table2.updateuserid = table3.empid? etc.

[ichervin]

I have figured out how to do this. But I have anothye problem, I am trying to use two joins and I am getting the error.

My query:

select emp.lastname, vp.stationid, vp.shiftcode, tr.priorityid,
tr.entrydate,em.lastname, tr.updatedate, empl.lastname
from employee em right outer join transferrequests tr
on em.userid = tr.entryuserid,
employee empl right outer join transferrequests tr
on empl.userid = tr.updateuserid,
employee emp, validpositions vp
where emp.empid = tr.empid and
vp.positionid = tr.positionid



Error message:

Server: Msg 1011, Level 15, State 1, Line 6
The correlation name 'tr' is specified multiple times in a FROM clause.


I understand what the error is, but I don't know how to fix it. Please help.

[skhanal]

If you are joining transferrequests more than once in the query, you have to use different alias.

[ichervin]

Thanks. I'll try that.