Link to home
Start Free TrialLog in
Avatar of Member_2_1242703
Member_2_1242703

asked on

SQL Query help

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?
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

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.
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Member_2_1242703
Member_2_1242703

ASKER

Thanks Ryan!