Tables Join Problem - 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 2005 Forums

Transact-SQL (2005)

Tables Join Problem

Author

Topic

paultervit
Starting Member

10 Posts

Posted - 2008-07-09 : 09:13:36

Hello All
Sorry if this is a bit of a basic question for most of you but I'm having a bit of trouble with a query I'm writing.

The query I have so far is as follows

select pl.PracticeCode
, pl.practicename
, u.username
, a.testid
from dbo.practicelookup pl
inner join dbo.[user] u
on ( pl.PracticeCode = left(u.username, 5) )
left outer join dbo.testaudit A
on u.userid = a.userid
where Month(a.eventtime) = 06
AND Year(a.eventtime) = 2008
order by pl.PracticeCode

What I'm trying to do is get all values from praticelookup and user where the first join matches and then list these with all details in the testaudit table for June this year. The problem is when I add the where clause it filers out all practicelooup tables where no entry is available for testaudit. e.g. I'm getting

PC PN UN TI
1 rt ty 12

when I really want

PC PN UN TI
1 rt ty 12
2 rt ty 0

Hope this makes sense, but any help would really help me.

Thanks

Paul

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-09 : 09:16:30

[code]SELECT pl.PracticeCode
, pl.practicename
, u.username
, a.testid
FROM dbo.practicelookup pl
INNER JOIN dbo.[user] u ON pl.PracticeCode = left(u.username, 5)
left OUTER JOIN dbo.testaudit A ON u.userid = a.userid
AND MONTH(a.eventtime) = 06
AND YEAR(a.eventtime) = 2008
ORDER BY pl.PracticeCode[/code]

KH
[spoiler]Time is always against us[/spoiler]

paultervit
Starting Member

10 Posts

Posted - 2008-07-09 : 09:21:08

thats the one thanks a lot khtan.....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources