inner join

[tariqi2011]

Hi
i want to join two tables,'Employee' and 'Dispatch'.
Dispatch has column 'DispatcherID' and 'TechnicianID' which are both foregn keys to EmployeeID in Employee table.I want to join these two tables using EmployeeID so that i can obtain the matching name to each id.but it only works when i make a single join to either DispatcherID or TechnicianID and not for both,please assist

select Employee.firstname+' '+Employee,secondname as Technician,Employee.firstname+' '+Employee.secondname as Dispatcher from Dispatch inner join Employee on Dispatch.TechnicianID=Employee.EmployeeID inner join Employee on Dispatch.DispatcherID=Employee.EmployeeID

[Reverend Jim]

Can you please clarify? An inner join is used to create a virtual (temporary) table that is the result of combining two other tables based on a (single) common column. Perhaps if you posted the schema of the two tables, and the schema of the virtual table you want as a result, your intentions would be clearer.

A good explanation of inner and outer joins can be found here

[tariqi2011]

Employee table
-EmployeeID PK
-FirstName
-SecondName

DispatchTable
-SequenceID PK
-DispatchID references Employee(EmployeeID)
-TechnicianID references Employee(EmployeeID)

the join should bring the following virtual table,joined on EmployeeID

Virtual Table
-DispatcherName(Employee.firstname+' '+Employee.secondname)
-TechnicianName(Employee.firstname+' '+Employee.secondname)

i hope its now clearer.

[Reverend Jim]

What you are basically doing is creating two separate but similar tables. You can't join two tables on two different columns (DispatcherID-EmployeeID in one case and TechnicianID-EmployeeID in the other). But, what you can do is a union. To do that you create one virtual table with one join and "add" it to another virtual table. The syntax for that is (I used slightly different column names in my example)

use mydb

select fullname = firstname + ' ' + lastname 
  from Employee,dispatch 
 where Dispatch.DispatchID = Employee.EmployeeID
union
select fullname = firstname + ' ' + lastname 
  from Employee,dispatch 
 where Dispatch.TechnicianID = Employee.EmployeeID

which results in a table of full names of people who are either dispatchers or technicians. If that isn't what you intended then perhaps an English description (no code, no tables, etc) such as the one at the beginning of this sentence.

[tariqi2011]

thanks, but this wont work because i dont need two tables,i need a one virtual table that pulls the dispatcher and the technician and put this on one gridview.
from this information the user will be able to know the technician that attended to a call and who dispatched that technician to that call.and even print a report.

[Reverend Jim]

This is interesting. There should be a way to do this in one query. In the meantime, you can generate the results by brute force as

        Dim con As New ADODB.Connection
        Dim rec As New ADODB.Recordset

        con.Open("Driver={SQL Server};Server=.\SQLEXPRESS;Database=mydb;Trusted_Connection=yes;")

        'get list of employee names and IDs

        Dim emp As New Dictionary(Of Integer, String)
        rec.Open("select EmployeeID,Name = FirstName + ' ' + SecondName from Employee", con, CursorTypeEnum.adOpenForwardOnly)

        Do Until rec.EOF
            emp.Add(rec("EmployeeID").Value, rec("Name").Value)
            rec.MoveNext()
        Loop

        rec.Close()

        'generate list of calls

        rec.Open("select SequenceID,DispatchID,TechnicianID from Dispatch", con, CursorTypeEnum.adOpenForwardOnly)

        Do Until rec.EOF
            Dim seqn As Integer = rec("SequenceID").Value
            Dim disp As String = emp(rec("DispatchID").Value)
            Dim tech As String = emp(rec("TechnicianID").Value)
            ListBox2.Items.Add(seqn & ": DISP: " & disp & " TECH: " & tech)
            rec.MoveNext()
        Loop

        rec.Close()
        con.Close()

I'll keep looking.

[Reverend Jim]

By the way, in order for the above code to work you also have to add a reference (under the Add Reference -> .NET tab or project properties) to adodb and include the following in your code:

Imports ADODB

[tariqi2011]

still, this is not what I want but thanks.

[adam_k]

You need to join to the Employee table twice (one for each name you want returned).

Select a.FirstName + ' ' + a.SecondName as 'Technician', b.FirstName + ' ' + b.SecondName as 'Dispatcher' 
from Dispatch inner join Employee a 
on TechnicianID = a.EmployeeID 
inner join Employee b 
on DispatchID = b.EmployeeID

So basically you are creating 2 "virtual tables" as you call them and then a third one out of these 2.

[scudzilla]

Are you looking for something similar to this?

"Select SequenceID, a.FirstName + ' ' + a.Secondname AS Dispatcher, b.FirstName + ' ' + b.Secondname as Technician from Dispatch, Employee a, Employee b where DispatchID like a.EmployeeID and TechnicianID like b.EmployeeID order by SequenceID"

[Reverend Jim]

Your best bet, because it looks like this will be done repeatedly, is to create a view as follows:

create view  DispatchV as
    select   Dispatch.SequenceId
            ,Dispatch.DispatchID
            ,Dispatch.TechnicianID
            ,DispatchName = DE.FirstName + ' ' + DE.SecondName
            ,TechnicianName = TE.FirstName + ' ' + TE.SecondName
    from Dispatch
left outer join Employee as DE
                on DE.EmployeeId = Dispatch.DispatchID
left outer join Employee as TE
                on TE.EmployeeId = Dispatch.TechnicianID;

Now when you want to generate the desired report you can query DispatchV as follows

select * from DispatchV

with the following (sample) output

SequenceID DispatchID TechnicianID DispatchName TechnicianName
1          1          6            Jim Johnson  Rodney Rippey
2          4          3            Mary Hartman George Jefferson
3          2          5            Fred Stahl   Louise Effers

Please note that you only have to create the view once.

[Reverend Jim]

PS: If you can't (or don't want to) create a view you can just do the query directly as

select   Dispatch.SequenceId
    ,Dispatch.DispatchID
    ,Dispatch.TechnicianID
    ,DispatchName = DE.FirstName + ' ' + DE.SecondName
    ,TechnicianName = TE.FirstName + ' ' + TE.SecondName
from Dispatch
    left outer join Employee as DE on DE.EmployeeId = Dispatch.DispatchID
    left outer join Employee as TE on TE.EmployeeId = Dispatch.TechnicianID

[tariqi2011]

thanks alot guys,the solution by scudzilla worked,by creating two virtual tables in the select statement,such that we have Employee a and Employee b.the join statement sees these as two different tables.
thanks rev jim and adam k

"Select SequenceID, a.FirstName + ' ' + a.Secondname AS Dispatcher, b.FirstName + ' ' + b.Secondname as Technician from Dispatch inner join Employee a on Dispatch.EmployeeID=a.EmployeeID inner join Employee b on Dispatch.EmployeeID=b.EmployeeID"