Posted by rhaazy on 10/02/34 11:51

using mssql 2000...

Table Def...


CREATE TABLE [dbo].[tblEmployee] (
[EmpID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[EmployeeID] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
,
[DateCreated] [datetime] NULL ,
[LastModified] [datetime] NULL ,
[Deleted] [bit] NULL
) ON [PRIMARY]


CREATE TABLE [dbo].[tblEmployeeOrgNode] (
[EmpID] [int] NOT NULL ,
[OrgSystemID] [int] NOT NULL ,
[OrgNodeID] [int] NOT NULL ,
[DateCreated] [datetime] NULL ,
[LastModified] [datetime] NULL ,
[Deleted] [bit] NULL
) ON [PRIMARY]


CREATE TABLE [dbo].[tblOrgSystemNode] (
[OrgSystemID] [int] NOT NULL ,
[OrgNodeID] [int] NOT NULL ,
[OrgNode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[DateCreated] [datetime] NULL ,
[LastModified] [datetime] NULL ,
[Deleted] [bit] NULL
) ON [PRIMARY]


When I run this query:
select Employeeid, tblEmployee.EmpID, OrgNode
from tblEmployee JOIN tblEmployeeOrgNode a
on tblEmployee.EmpId = a.EmpID,
tblOrgSystemNode JOIN tblEmployeeOrgNode b
on tblOrgSystemNode.OrgNOdeId = b.OrgNodeID


I get this as a result set:
Name ID Location
rh 1 Mano
rm 2 Mano
rd 3 Mano
rh 1 Huso
rm 2 Huso
rd 3 Huso
rh 1 Oso
rm 2 Oso
rd 3 Oso


This is what I am trying to get:
Name ID Location
rh 1 Mano
rm 2 Huso
rd 3 Oso

[Back to original message]