Left outer join question

Question

Post reply

Left outer join question

kevin.lanners

SSC Veteran

Points: 257
More actions
July 28, 2010 at 8:50 am

#221816

I'm trying to write a query but I'm stumped.
Here's the scenario. I have two tables Table1 is a list of all employees, Table2 is an audit table where a record is written when a user submits a timesheet. What I want to do is write a query that shows hourly employees (Table1.PayType = 'H') who have or HAVE NOT submitted a timesheet with an identifier (Pay Period date?) showing if the timesheet exists or not. The have not part is where I'm getting hung up.
In the most basic sense, I'd like the query below to work but returning everything from PayType.
SELECT dbo._PayType.FirstName, dbo._PayType.LastName, dbo.Timesheet_Activity.PayPeriod
FROM dbo._PayType LEFT OUTER JOIN
dbo.Timesheet_Activity ON dbo._PayType.EmpID = dbo.Timesheet_Activity.EmpID
WHERE (dbo._PayType.PayType = 'H') AND (dbo.Timesheet_Activity.PayPeriod = @PayPeriod)
Thanks,

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

You should learn how to use table aliases and nicer formatting to make your query a bit more readable.

Try this:

SELECT PT.FirstName, PT.LastName, @PayPeriod AS PayPeriod

FROM dbo._PayType AS PT

LEFT JOIN dbo.Timesheet_Activity AS TA

ON PT.EmpID = TA.EmpID

AND TA.PayPeriod = @PayPeriod -- as you only want to join to the Timesheet_Activity record for the relevant period

WHERE PT.PayType = 'H'

AND TA.EmpID IS NULL -- that what you check!,

-- so if there is a record in Timesheet_Activity

-- for the employee who submitted timesheet for

-- @PayPeriod - TA.EmpID will not be null

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Paul White SSC Guru Points: 150468 More actions · Answer 2

Very slight variation on the theme:

SELECT P.FirstName,

P.LastName,

PayPeriod = ISNULL(TA.PayPeriod, '?')

FROM dbo._PayType P

LEFT

OUTER

JOIN dbo.Timesheet_Activity TA

ON P.EmpID = TA.EmpID

AND TA.PayPeriod = @PayPeriod

WHERE P.PayType = 'H';

Sorry if I misunderstood the requirement.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

kevin.lanners SSC Veteran Points: 257 More actions · Answer 3

CELKO (7/29/2010)
I am confused. You have a table name that begins with an underscore which is illegal in Standard SQL and in most programming languages. It is called "PayType" as if you have only one such code. When did an attribute become an entity? Then this table of types includes the name of personnel while their time sheets do not. There is no Personnel table, either.
Isn't this fundamentally wrong?

Thank you for your condescension. I applaud you for not letting the fact that the only thing you know about the entities are the elements included in my (poorly redacted) queries stop commenting on their structure.

Everyone else, thanks for your input. I haven't had time to come back to this task yet so I will review these suggestions and let you know how it goes.