modify query or new table ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

modify query or new table ?

Author

Topic

kaus
Posting Yak Master

179 Posts

Posted - 2002-03-08 : 14:39:02

Hi - I have the following problem:
I want to get Man_Name for the secondary Manager in the query below.
Secondary manager is either one of the managers
in the managers table or is often null
the manager, however, cannot be null

Do I need to make a secondary managers table ?? I
hate to do this because it will mess up a lot
of other things within the application

Projects Table
Proj_ID
ProjName
Man_ID --> refers to Man_ID in Managers
Second_Man_ID --> refers to Man_ID in Managers

Managers Table
Man_ID
Man_Name

Query:
select
Proj_Type_Name, Man_Name, ?<second_Man_Name>?, Proj_Name
from
Managers, Projects
where
Managers.Man_ID = Projects.Man_ID
and Projects.Month_ID = @strMonthId

Thanks

Pete

Jay99

468 Posts

Posted - 2002-03-08 : 14:46:16


select
    p.Proj_Type_Name,
    m1.Man_Name,
    m2.Man_Name as second_Man_Name,
    p.Proj_Name 
from
    Projects p
    inner join Managers m1
        on p.Man_ID = m1.Man_ID
    innner join Managers m2
        on p.Second_Man_ID = m2.Man_ID
where
    p.Month_ID = @strMonthId

Jay

kaus
Posting Yak Master

179 Posts

Posted - 2002-03-08 : 16:16:39

Thanks - that works great. You need to join managers to projects twice -- once for each man and second_man_id. I need to look at that inner/outer join stuff a little more. I always just use the simple = connection

Pete

Jay99

468 Posts

Posted - 2002-03-08 : 16:36:31

quote:

...I need to look at that inner/outer join stuff a little more...
Pete

All the kewl kids are doin' it

Jay

kaus
Posting Yak Master

179 Posts

Posted - 2002-03-08 : 17:24:28

Unfortunately I have more than these two tables and am trying to use this syntax with nesting -- (now I remember why I used the = where clause.) I have the following tables:
Projects
Managers
Months
Project_Types
Goals
Accomplishments

I'm trying the following query and keeps getting spit back at me
select
project_types.Proj_Type_Name, m1.Man_Name, m2.man_name as second_man_name,
projects.Proj_Name, goals.Goal_Desc,
accomplishments.Accomp_desc, projects.RPT_Month,
project_types.Project_Types.Proj_Type_ID
from
Projects
inner join Managers m1
on projects.Man_ID = m1.Man_ID
left outer join Managers m2
on projects.Second_Man_ID = m2.Man_ID
inner join project_types
on projects.project_type_id = Project_Types.Proj_Type_ID
inner join months
on projects.month_id = months.month_id
inner join goals
on projects.proj_id = Goals.Proj_ID
Goals
inner join accomplishments
on Goals.Goal_ID = Accomplishments.Goal_ID
where
projects.month_id = 2

I know somehow I have to nest projects and goals -- any help would be greatly appreciated

Thanks

Pete

Lou
Yak Posting Veteran

59 Posts

Posted - 2002-03-08 : 17:34:01

quote:

inner join goals
on projects.proj_id = Goals.Proj_ID
Goals
inner join accomplishments
on Goals.Goal_ID = Accomplishments.Goal_ID
where
projects.month_id = 2

It would help if you post the error message. Anyways, I took a quick look. Try getting rid of "Goals" after "on projects.proj_id = Goals.Proj_ID". HTH.

kaus
Posting Yak Master

179 Posts

Posted - 2002-03-08 : 18:12:45

thanks -
that gets the query to run -- there were some other typos also --
with the table structure I have I need to run a nested query --
the accomplishments table is related to the goals table but not the projects table so I get a cartesian product. Im trying to find an example of a nested join -- I've looked at BOL and in Access but there is only the syntax -- which I'm having trouble deciphering -- maybe I'll try the query builder in Access ---

Pete

Lou
Yak Posting Veteran

59 Posts

Posted - 2002-03-08 : 19:23:16

Kaus,

By nested query, I guess you're talking about derived tables. Derived tables are much faster than sub-queries. Look up Nazim's posts. He is the king of derived queries.

Edited by - lou on 03/08/2002 19:23:59

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources