Trying to join two tables...
-
This is probably a very simple question, but I've been trying to figure this out for hours and it's KILLING ME!
I have two tables with a relation of a username. One table has usernames (call it Uers), the other has usernames,time entry values, and time entry dates (call it Hours).
I need to join the two tables on the username and total the time entry between a certain date range. So my starting query was:
SELECT u.username, SUM(h.hour)
FROM users u
JOIN hours h ON (u.username = h.username)
WHERE h.entrydate BETWEEN getdate() -30 AND getdate()
GROUP BY u.username
So I get all users with their total times in this period. Awesome. However, I need all of the users with no times in this period as well. So I need the names from users, but a NULL for time. I just want them to show up in the list.
How can I get everyone from the Users table to show up in this join, even if they have no hours for the period? I tried a LEFT OUTER JOIN, but I'm pretty sure that there is no inequalities showing up because of my JOIN ON criteria...and since the users exist in both tables, everything matches up.
Can anyone help a novice? thanks!
-
it's actually pretty easy; you were almost there
SELECT u.username, SUM(h.hour)
FROM users u
JOIN hours h ON (u.username = h.username)
WHERE (h.entrydate BETWEEN getdate() -30 AND getdate()) OR h.entrydate is null
GROUP BY u.username
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
thanks very much, I'll remote in and give it a shot.
-
This worked. Thanks very much.
-
Remember that GETDATE() returns a date-TIME. Are you sure that this query is giving you exactly what you want or are you losing records at the extremes?
If I run GETDATE() now, it returns 2006-12-06 13:54:14.870. GETDATE() - 30 would miss anything prior to 2006-11-06 13:54:14.870, eg anything on the morning of 6 November or without any time attached at all (depending on how you hold your data).
-
Yes, I intend to go back and change that. I had some problems with the date format I was usings, so I switched to GETDATE() just to get some results. It turns out it may have been a problem with the data and not my syntax (i.e. we were looking at the wrong table), but it should be fixed soon. Thanks for the help everyone.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply