SQL Query help

Mike Miller
Mike Miller used Ask the Experts™
on
I have this query pulling from two tables. It essentially sums the column of a bunch of records then adds some more relevant data where the field EMPLID matches

SELECT        TOP (100) PERCENT t1.FULLNAME, t2.TOTAL_HOURS, t2.EMPLID, t1.MGRID, t2.FiscalYear, t1.VPID
FROM            myDB.dbo.USERSS AS t1 INNER JOIN
                             (SELECT        EMPLID, SUM(Hours) AS TOTAL_HOURS, FiscalYear
                               FROM            dbo.REQUESTS
                               GROUP BY EMPLID, FiscalYear) AS t2 ON t2.EMPLID COLLATE Latin1_General_BIN = t1.EMPLID
WHERE        (t1.DELETED = 'N')

Open in new window


Is there a way for me to pull the same data but also have a record for each entry from the USERS table where there isn't anything found for that record's EMPLID in the REQUESTS table, and just set the SUM of TOTAL_HOURS to 0.00?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

Commented:
Is there a way for me to pull the same data but also have a record for each entry from the USERS table where there isn't anything found for that record's EMPLID in the REQUESTS table, and just set the SUM of TOTAL_HOURS to 0.00?
yes, try use left join instead of inner join.
Software Team Lead
Commented:
like
SELECT        TOP (100) PERCENT t1.FULLNAME, isnull( t2.TOTAL_HOURS,0) TOTAL_HOURS, t2.EMPLID, t1.MGRID, t2.FiscalYear, t1.VPID
FROM            myDB.dbo.USERSS AS t1 left JOIN
                             (SELECT        EMPLID, SUM(Hours) AS TOTAL_HOURS, FiscalYear
                               FROM            dbo.REQUESTS
                               GROUP BY EMPLID, FiscalYear) AS t2 ON t2.EMPLID COLLATE Latin1_General_BIN = t1.EMPLID
WHERE        (t1.DELETED = 'N')

Open in new window

Mike MillerSoftware Engineer

Author

Commented:
Thanks Ryan!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial