Multiple join doesn't show the required data

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 2005 Forums

Transact-SQL (2005)

Multiple join doesn't show the required data

Author

Topic

ramzansadiq
Starting Member

6 Posts

Posted - 2009-01-28 : 23:56:30

Hello to everybody,
I am unable to get the required data when filtering the Subject_ID using like however I get the right data without where clause.I have 5 tables as follows
Comm-Master (Comm_ID, Comm_Route,Comm_Date, etc)
Comm-Details (Comm_ID, Subject_ID, Employer_ID, Player_ID)
Subject(ID,Name)
Employer(ID,Name)
Player(ID,Name)

My query which works fine

SELECT F.Comm_ID, F.Comm_Date, S.SubjectName, D.PlayerName
FROM dbo.Comm F RIGHT OUTER JOIN
dbo.Comm_Details FD ON F.Comm_ID = FD.Comm_ID FULL OUTER JOIN
dbo.Player D ON FD.Player_ID = D.DPOH_ID FULL OUTER JOIN
dbo.Subject S ON FD.Subject_ID = S.Subject_ID

But this one with %like% doesn't show specific record, please please tell me what i m doing wrong or any work around I'll be greatfull for
you

My query which works fine

SELECT F.Comm_ID, F.Comm_Date, S.SubjectName, D.PlayerName
FROM dbo.Comm F RIGHT OUTER JOIN
dbo.Comm_Details FD ON F.Comm_ID = FD.Comm_ID FULL OUTER JOIN
dbo.Player D ON FD.Player_ID = D.DPOH_ID FULL OUTER JOIN
dbo.Subject S ON FD.Subject_ID = S.Subject_ID
Where D.PlayerName like %Roger%

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-29 : 00:12:48

SELECT F.Comm_ID, F.Comm_Date, S.SubjectName, D.PlayerName
FROM dbo.Comm F
RIGHT OUTER JOIN dbo.Comm_Details FD ON F.Comm_ID = FD.Comm_ID
FULL OUTER JOIN dbo.Player D ON FD.Player_ID = D.DPOH_ID
and D.PlayerName like %Roger%
FULL OUTER JOIN dbo.Subject S ON FD.Subject_ID = S.Subject_ID

E 12°55'05.63"
N 56°04'39.26"

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-29 : 00:13:27

try this one,

SELECT F.Comm_ID, F.Comm_Date, S.SubjectName, D.PlayerName
FROM dbo.Comm F RIGHT OUTER JOIN
dbo.Comm_Details FD ON F.Comm_ID = FD.Comm_ID FULL OUTER JOIN
dbo.Player D ON FD.Player_ID = D.DPOH_ID FULL OUTER JOIN
dbo.Subject S ON FD.Subject_ID = S.Subject_ID
Where D.Name like %Roger%

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources