Select command joing multiple table...

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)

Select command joing multiple table...

Author

Topic

thiruna
Starting Member

41 Posts

Posted - 2003-05-31 : 01:29:40

I have the following tables...

1. Division
div_no-----------div_name
1----------------div01
2----------------div02

2.company
comp_no----------comp_name
1----------------comp1
2----------------comp2

3.users
user_no----------comp_no-----------div_no-----user_admin
1----------------1-----------------1----------1
2----------------1-----------------1----------2
3----------------1-----------------2----------1
4----------------1-----------------2----------1
5----------------2-----------------1----------1
6----------------2-----------------1----------1
7----------------2-----------------2----------1
8----------------2-----------------2----------1

4.projects
proj_no----------proj_name---------div_no
1----------------prj01-------------1
2----------------prj02-------------1
3----------------prj03-------------2
4----------------prj04-------------2
5----------------prj05-------------2

5.company_projects
comp_no----------proj_no-----------include(yes-1/No-0)
1----------------1-----------------1
1----------------2-----------------1
1----------------3-----------------1
1----------------4-----------------0
1----------------5-----------------1
2----------------1-----------------0
2----------------2-----------------0
2----------------3-----------------0
2----------------4-----------------1
2----------------4-----------------1

Now i want to list the projects for user_no=1(i mean it should select the comp_no and div_no from the users table and then select the projects for the company where include =1 from company_project table and then filter the results for the users division.)

Here for this query("list projects for user_no=1), it should return the following rows.

user_no------comp_no--------div_no----------proj_no---div_no
1------------1--------------1---------------1---------1
1------------1--------------1---------------2---------1

and for this query("list projects for user_no=2 who has user_admin=2), it should return the following rows.

user_no------comp_no--------div_no----------proj_no-----div_no
1------------1--------------1---------------1-----------1
1------------1--------------1---------------2-----------1
1------------1--------------1---------------3-----------2
1------------1--------------1---------------5-----------2

I have tried with the following select command...

SELECT usr.user_no, usr.comp_no, usr.div_no, prj.proj_no, prj.div_no
FROM users usr
INNER JOIN projects prj ON usr.div_no =prj.div_no
INNER JOIN company_projects comp ON prj.proj_no =comp.proj_no
WHERE prj.div_no=isnull(SELECT case when user_admin=2 Then null Else division_no end FROM users WHERE user_no =1), prj.div_no))
AND comp.include=1

But above statement doesnt return the required results... Can you please help me to frame a correct statement?

Many thanks in advance
Thiru
thiru.nachi@cloughindia.com

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-31 : 03:13:02

SELECT usr.user_no, usr.comp_no, usr.div_no, prj.proj_no, prj.div_no

FROM users usr

INNER JOIN company_projects comp ON usr.comp_no =comp.comp_no

INNER JOIN projects prj ON prj.proj_no=comp.proj_no AND prj.div_no = usr.div_no

WHERE usr.user_no = 1 AND comp.include = 1

You don't describe what user_admin is about, but your WHERE suggests that you don't want rows with user_admin = 2? Just add AND usr.user_admin <> 2 to the WHERE.

Sam

thiruna
Starting Member

41 Posts

Posted - 2003-05-31 : 03:45:25

User_admin is the administrator level and here it is as follows...

Normal user =1
Administrator =2

So when listing projects, it should list all projects from all divisions for that company if user_admin is 2. (Note: it should pick up the user_admin value by itself using user_no)

any suggesstions please.
thanks
thiru

Edited by - thiruna on 05/31/2003 03:46:32

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-31 : 09:21:37

SELECT usr.user_no, usr.comp_no, usr.div_no, prj.proj_no, prj.div_no

FROM users usr

INNER JOIN company_projects comp ON usr.comp_no = comp.comp_no

INNER JOIN projects prj ON prj.proj_no = comp_proj_no

WHERE prj.div_no=isnull(SELECT case when user_admin=2 Then null Else division_no end FROM users WHERE user_no =1), prj.div_no))

AND comp.include=1 AND usr.user_no = 1

Sam

thiruna
Starting Member

41 Posts

Posted - 2003-06-01 : 04:46:25

Thanks Sam,

Is there any good link to learn abour Inner join in details...

Thanks agn. for your support...

Thiru

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-06-01 : 07:49:49

Books Online is probably your best bet.

Or one of the books in the SQLTeam Store. "Professional SQL Server 2000 Programming" is probably a good choice.

Damian

Edited by - merkin on 06/01/2003 07:50:13

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources