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

rhaazy (rhaazy@gmail.com) writes:
> 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
>...
>
> This is what I am trying to get:
> Name ID Location
> rh 1 Mano
> rm 2 Huso
> rd 3 Oso

It's good to have the table, but with out sample data and an explanation
of the table, it's difficult to make much useful out of it. It had also
help if you had included definitions of primary keys and foreign keys.

But I note your query includs a cross join with tblOrgSystemNode. This
looks suspicious. Maybe that is an error. So as a complete guess

select E.Employeeid, E.EmpID, S.OrgNode
from tblEmployee E
JOIN tblEmployeeOrgNode EON on E.EmpId = EON.EmpID,
JOIN tblOrgSystemNode S ON S.OrgNOdeId = EON.OrgNodeID

If this does not meet your requirements, please supply sample data
(as INSERT statements) and briefly explain the business rules.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Sorry for not including the rest, but you hit the nail right on the
head anyway, thanks a lot that did the trick.


Erland Sommarskog wrote:
> rhaazy (rhaazy@gmail.com) writes:
> > 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
> >...
> >
> > This is what I am trying to get:
> > Name ID Location
> > rh 1 Mano
> > rm 2 Huso
> > rd 3 Oso
>
> It's good to have the table, but with out sample data and an explanation
> of the table, it's difficult to make much useful out of it. It had also
> help if you had included definitions of primary keys and foreign keys.
>
> But I note your query includs a cross join with tblOrgSystemNode. This
> looks suspicious. Maybe that is an error. So as a complete guess
>
> select E.Employeeid, E.EmpID, S.OrgNode
> from tblEmployee E
> JOIN tblEmployeeOrgNode EON on E.EmpId = EON.EmpID,
> JOIN tblOrgSystemNode S ON S.OrgNOdeId = EON.OrgNodeID
>
> If this does not meet your requirements, please supply sample data
> (as INSERT statements) and briefly explain the business rules.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx