July 9, 2008 at 4:59 am
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
frompracticelookup pl inner join user u
on (pl.PracticeCode = left(u.username, 5))
left outer join testaudit A on u.userid = a.userid
where Month(a.eventtime) = 06
AND Year(a.eventtime)= 2008
order by 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
July 9, 2008 at 5:24 am
Welcome.
First, please read this - http://www.sqlservercentral.com/articles/Best+Practices/61537/
If you can give us your table structure, a sample of the data in it and an indication of the results you want, I'm sue someone will help you very quickly.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 9, 2008 at 5:47 am
be sure to include the objects schema ! (even if it is dbo)
don't use reserved words in your objects or always use brackets !
In your case it was object "user" that messed it up !
select pl.PracticeCode
, pl.practicename
, u.username
, a.testid
from dbo.practicelookup pl
inner join dbo. 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
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 10, 2008 at 6:27 am
I can give you a hint, but like the others say, for a real solution, more information would be handy.
The hint is that in your where clause, you are forcing your join into becoming an inner join. In other words, only those records that match the join condition will ever have a value of 06 or a year.
So, move the where clause into the join. Something like
select pl.PracticeCode, pl.practicename, u.username, a.testid
from practicelookup pl inner join user u
on (pl.PracticeCode = left(u.username, 5))
left outer join testaudit A on u.userid = a.userid AND Month(a.eventtime) = 06 AND Year(a.eventtime)= 2008
order by practicecode
This will return all the practicelookup records with matching user records. It will return a null testid, unless there is a match on userid with a june/2008 value.
The other way to do it is to keep the where clause, but make the where clause allow for null values as well as 06/2008.
Hope that helps!
--Todd
July 10, 2008 at 6:36 am
Hi.,
Can you post the table structure with some sample data and your required output..
Thanks.,
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply