Need Query - 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

General SQL Server Forums

New to SQL Server Programming

Need Query

Author

Topic

yaman
Posting Yak Master

213 Posts

Posted - 2009-09-18 : 11:00:23

Sir ,

I have three tables
.
CREATE TABLE [dbo].[tbl_consultantWork](
[consultantid] [int] ,
[workstatusid] [int]
)

CREATE TABLE [dbo].[tbl_workstatus](
[Wrokstatusid] [int]

CREATE TABLE [dbo].[tbl_consultant](
[Consultantid] [int]
)

My tbl_consultantWork table data is .
Consultantid WorkstatusID
1 1
1 2
1 3
2 2
2 3
3 1
3 3

2nd table tbl_Workstatus data is
Wrokstatusid
1
2
3

3rd table tbl_consultant
consultantid
1
2
3
4

I am pass :- Workstatus id (2,3) comma seprated .
i want that all consultant record whose workstatusid of table tbl_Workstatus is exactlty match with workstatusID of table tbl_consultantWork, not less not more .

SELECT distinct TCW.consultantid , TCW.workstatusid
From tbl_consultant as TC inner join tbl_ConsultantWork as TCW
on TC.Consultantid=TCW.consultantid
INNER JOIN tbl_WorkStatus as WS on WS.Wrokstatusid=TCW.workstatusid and
WS.Wrokstatusid in (2,3)

Result is come by query which is wrong :-
Consultantid WokstatusID
1 2
1 3
2 2
2 3
3 3 ( Not need this row because we dont have workstatusid 2 for consultant 3 in table tbl_consultantWork)

Please help me out .

Yaman

X002548
Not Just a Number

15586 Posts

Posted - 2009-09-18 : 12:02:20

WHY is it wrong?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

yaman
Posting Yak Master

213 Posts

Posted - 2009-09-18 : 12:16:01

Because my result is wrong .
I want that record whose workid is matched for both workstatus id
( 2,3 ) like

1 view of dummy result

consultantid workstatusid
1 2 right
1 3 right

2 view of dummy result

consultantid workstatusid
1 3 wrong because i need whose consultant record who
have record for both workstatusid ( 2, 3)

3rd view of my dummy result

consultantid workstatusid
1 2 wrong because i need whose consultant record who
have record for both workstatusid ( 2, 3)

quote:
Originally posted by X002548

WHY is it wrong?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Yaman

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources