How do I reference the same file more than once?

[steved45]

Please bear with me I'm very new to SQL / Server and am jumping in to a project.

I'm building a report in MS Report Builder 3.0. In the Dataset Query to assemble the fields I'll use I have to join 4 files / tables (this is on an i5 DB2400 in ODBC). In the database there is one personnel file that has an ID# and a NAME. From a CASE file I will have an Officer and a Supervisor (I work for a PD). I wish to display these only once in a row - ie

OFFicer = DANNO Supervisor = Steve McGarrett when I do the innerjoin to this with an OR I get two rows with the different names for each case. On the report, I only want to have the Officer Name and the Supervisor Name with their ID #s and the other data in one row.

Help?

 

[SQLScholar]

Are you doing one inner join for both. You should be doing two joins

so something like this.

select 
*
from 
casefile
inner join 
personnel 
on
casefile.offericerid = personnel.personnelid
inner join 
personnel supervisor
on
casefile.supervisorid = supervisor.personnelid

Notice how there is a second join to the personnel table. When you reference the same table twice you must give it an alias. In this case i have called it supervisor.

HTH

Dan

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[steved45]

Works perfectly - THanks!

 

[steved45]

Spoke too soon - it works in the query test but then gives me an error message when I try to save it: "An item with the same key has already been added."

SELECT DISTINCT
csori#,
cscas#,
oacas#,
csstat,
oaofic,
oaasst,
oaasby,
oaaymd,
tedesc,
e.ombdg#,
e.omlnam,
e.omfnam,
s.ombdg#,
s.omlnam,
s.omfnam
from

CASE

JOIN CSAS ON cscas#=oacas#
JOIN TABE ON tecod#=oaasst
JOIN OFCR E ON oaofic=e.ombdg# "e=Officer"
JOIN OFCR S ON oaasby=s.ombdg# "s=supervisor"

where (cscas# between '201000030000' and '201000033999')
AND (TEMST# = 94)

order by cscas#