I am trying to gather information by comparing two tables.  Table A and Table
B both have the field shop_person.  Table A is the entire list of employees,
table B contains data about attendence, designated by a code.  I want to
compare the two tables and select those people from table A who are not in
table B with regards to a certain code.  Simplistically, it's like - if
A.shop_person doesn't exist in B.shop_person, then they were absent.

I'll use the stored procedure to create a report.  Thanks for any help.


Darlene

SELECT TableA.* FROm TableA LEFT JOIN TABLEB ON
TableA.shop_person=TableB.shop_person 
WHERE TableB.Code=whatever AND TableB.shop_person IS NULL



Show quoteHide quote

/*
I am trying to gather information by comparing two tables.  Table A and Table
B both have the field shop_person.  Table A is the entire list of employees,
table B contains data about attendence, designated by a code.  I want to
compare the two tables and select those people from table A who are not in
table B with regards to a certain code.  Simplistically, it's like - if
A.shop_person doesn't exist in B.shop_person, then they were absent.

I'll use the stored procedure to create a report.  Thanks for any help.
*/


Create table #a
(
emp_id    int,
shop_person  sysname
)

Create table #b
(

theDay      datetime,
code        nvarchar(10),
shop_person sysname
)

Insert #a
Values (1,'Me')

Insert #a
Values (2,'Me2')

Insert #a
Values (3,'Me3')

Insert #b
Values (getdate()-2,'1001','Me')  --1001 is here


Insert #b
Values (getdate()-1,'1001','Me')  --1001 is here

Insert #b
Values (getdate(),'1001','Me')  --1001 is here

select * from #a
where not exists(select shop_person from #b where #a.shop_person =
#b.shop_person)

--results these guys are not here
2    Me2
3    Me3


Hope this helps you sort it out.


Show quoteHide quote

Thanks to both of you.  I got it to work.


Show quoteHide quote

Case Statement?
View Explaination
Index Question
Warning: Null value is eliminated (causing sproc to fail)
Query urgent