SELECT th.ID, tp.FirstName + ' ' + tp.LastName as Name, IsNull(Total_Hours, '0.0') as Hours1
FROM tblProfiles tp LEFT JOIN tblHours th on tp.ID = th.userID
WHERE (th.Start_Date = '" + myStartDate + "' OR th.Start_Date IS NULL)
ORDER BY tp.FirstName
For testing, in my profiles table, I have 5 users. In my hours table, I have entries for 3 of those users. Of those three users, 2 have entries for both 1/1/03 and 1/16/03, and the other has just 1 entry for 1/1/03.
When I run the query above for myStartDate = '1/1/03', I get the correct output, which shows the hours for 3 users, and shows 0 hours for the other 2. However, if I run the query on myStartDate = '1/16/03', my result set has the hours for the 2 users that have entries for that date, and also has 0 hours for the 2 users that have no entries. The user that has only 1 entry (for 1/1/03)should show up with 0 hours, but instead, it doesn't show up at all.
I'm guessing that the problem is with the way the table is joined, and that because that user has 1 valid Start_Date, it is getting excluded from the WHERE conditions, but I can't seem to make it work.
help!can you show the structure of the 2 tables ...|||tblProfiles has the following columns:
ID FirstName LastName ... (other unimportant columns)
tblHours has the following columns:
|||have you tried running this query in Query Analyzer as with the straight sql to make sure that you are getting the same results?|||yeah, I did... in Enterprise Manager, if I run the same query, I get the same results... the user with 1 entry for 1/1/03 does not show up at all if I run the query with:
ID userID Start_Date End_Date Total_Hours
WHERE Start_Date = '1/16/03' OR Start_Date IS NULL
I am assuming that "Start_Date IS NULL" is not evaluating to true because, with the table join, it is picking up the other record with the 1/1/03 Start_Date ...|||In my WHERE clause:
WHERE Start_Date = '" + myStartDate + "' OR Start_Date IS NULL
I think theStart_Date IS NULL part needs to specifically refer only the record for the given "myStartDate", but I can't make it jive...|||Yeah this is ringing a bell, I had something similiar. Maybe try wrapping everything in parens, like
WHERE ((Start_Date = '" + myStartDate + "') OR (Start_Date IS NULL))
Not sure but this might force SQL to evaluate the where clause correctly.|||I tried your suggestion, but it didn't change the results at all...|||have you thought abt using a cursor...
loop through for each record in tblprofiles...and for each record join with tblhours on id and start_date and get the hours..
this way you can get those ids tht dont have any entries...startdates/hours...etc and still show them.
HTH|||I'm not familiar with using cursors, but I'm willing to try anything... can you give me an example of how I would use it?|||heres a sample cursor tht i am using
DECLARE rs CURSOR
LOCAL
FORWARD_ONLY
OPTIMISTIC
TYPE_WARNING
FOR SELECT ID,FirstName,LastName, ... (other columns)
OPEN rsfetch next from rs into @.id,@.fname,@.lname...( other columns)
WHILE ( @.@.FETCH_STATUS = 0 )begin
/* here do your stuff - join with the other table and get the stuff */
use the @.id to join with tblhours..FETCH NEXT FROM rs INTO @.id,@.fname,@.lname...( other columns)
END
close rs
deallocate rs
HTH|||Ok, I'm getting lost in the syntax... here's what I have, but it's not working:
DECLARE @.id integer, @.fname varchar(50), @.lname varchar(50)
DECLARE rs CURSOR
LOCAL
FORWARD_ONLY
OPTIMISTIC
TYPE_WARNING
FOR SELECT tp.ID, tp.FirstName, tp.LastName FROM tblProfiles tp
OPEN rs
fetch next from rs into @.id,@.fname,@.lname
WHILE ( @.@.FETCH_STATUS = 0 )
begin
SELECT @.id, @.fname, @.lname
SELECT th.Total_Hours FROM tblHours th WHERE th.userID = @.id AND th.Start_Date='1/16/03'
fetch next from rs into @.id,@.fname,@.lname
END
close rs
deallocate rs
what am I doing wrong?|||I finally got it to work, and didn't have to use the cursor! Here's the working query:
SELECT IsNull(th.ID, '0') as ID, tp.FirstName + ' ' + tp.LastName as Name, IsNull(Total_Hours, '0.0') as Hours1
FROM tblProfiles tp LEFT JOIN tblHours th on tp.ID = th.userID AND th.Start_Date = '" + myStartDate + "'
WHERE ((th.Start_Date = '" + myStartDate + "') OR (th.Start_Date IS NULL)) ORDER BY th.Total_Hours DESC, tp.FirstName
The solution was to addStart_Date = '" + myStartDate + "' to the LEFT JOIN criteria.
Thanks for all the help along the way guys...
<sigh of relief>|||
CREATE PROCEDURE get_hrs AS
beginDECLARE @.id as int, @.fname as varchar(50), @.lname as varchar(50)
declare @.mystartdate as datetime
set @.mystartdate='1/1/2003'
declare @.hrs as int
DECLARE rs CURSOR
LOCAL
FORWARD_ONLY
OPTIMISTIC
TYPE_WARNING
FOR SELECT [ID], FName, LName FROM tblpro
OPEN rs
fetch next from rs into @.id,@.fname,@.lname
WHILE ( @.@.FETCH_STATUS = 0 )
beginselect @.hrs=hours from tblhours where ([ID] =@.id and startdate=@.mystartdate)
print convert(varchar(5),@.id) + ' ' + @.fname + '-' + @.lname + ' ' + convert(nvarchar(10),@.hrs)
set @.hrs=0fetch next from rs into @.id,@.fname,@.lname
ENDclose rs
deallocate rs
end
GO
you might need to change the column names, table names...
HTH
