Need help with this select statement

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • rhaazy
    #1

    Need help with this select statement

    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_Gene ral_CP1_CI_AS NULL
    ,
    [DateCreated] [datetime] NULL ,
    [LastModified] [datetime] NULL ,
    [Deleted] [bit] NULL
    ) ON [PRIMARY]


    CREATE TABLE [dbo].[tblEmployeeOrgN ode] (
    [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].[tblOrgSystemNod e] (
    [OrgSystemID] [int] NOT NULL ,
    [OrgNodeID] [int] NOT NULL ,
    [OrgNode] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
    NULL ,
    [DateCreated] [datetime] NULL ,
    [LastModified] [datetime] NULL ,
    [Deleted] [bit] NULL
    ) ON [PRIMARY]


    When I run this query:
    select Employeeid, tblEmployee.Emp ID, OrgNode
    from tblEmployee JOIN tblEmployeeOrgN ode a
    on tblEmployee.Emp Id = a.EmpID,
    tblOrgSystemNod e JOIN tblEmployeeOrgN ode b
    on tblOrgSystemNod e.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

    Tags: None
    • Erland Sommarskog
      #2
      Re: Need help with this select statement

      rhaazy (rhaazy@gmail.c om) writes:[color=blue]
      > When I run this query:
      > select Employeeid, tblEmployee.Emp ID, OrgNode
      > from tblEmployee JOIN tblEmployeeOrgN ode a
      > on tblEmployee.Emp Id = a.EmpID,
      > tblOrgSystemNod e JOIN tblEmployeeOrgN ode b
      > on tblOrgSystemNod e.OrgNOdeId = b.OrgNodeID
      >...
      >
      > This is what I am trying to get:
      > Name ID Location
      > rh 1 Mano
      > rm 2 Huso
      > rd 3 Oso[/color]

      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 tblOrgSystemNod e. This
      looks suspicious. Maybe that is an error. So as a complete guess

      select E.Employeeid, E.EmpID, S.OrgNode
      from tblEmployee E
      JOIN tblEmployeeOrgN ode EON on E.EmpId = EON.EmpID,
      JOIN tblOrgSystemNod e 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@sommarsk og.se

      Books Online for SQL Server 2005 at
      http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

      Books Online for SQL Server 2000 at
      http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

        Comment

        • rhaazy
          #3
          Re: Need help with this select statement

          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:[color=blue]
          > rhaazy (rhaazy@gmail.c om) writes:[color=green]
          > > When I run this query:
          > > select Employeeid, tblEmployee.Emp ID, OrgNode
          > > from tblEmployee JOIN tblEmployeeOrgN ode a
          > > on tblEmployee.Emp Id = a.EmpID,
          > > tblOrgSystemNod e JOIN tblEmployeeOrgN ode b
          > > on tblOrgSystemNod e.OrgNOdeId = b.OrgNodeID
          > >...
          > >
          > > This is what I am trying to get:
          > > Name ID Location
          > > rh 1 Mano
          > > rm 2 Huso
          > > rd 3 Oso[/color]
          >
          > 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 tblOrgSystemNod e. This
          > looks suspicious. Maybe that is an error. So as a complete guess
          >
          > select E.Employeeid, E.EmpID, S.OrgNode
          > from tblEmployee E
          > JOIN tblEmployeeOrgN ode EON on E.EmpId = EON.EmpID,
          > JOIN tblOrgSystemNod e 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@sommarsk og.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[/color]

            Comment

            Previous template Next
            Working...