Sql Statement logic help

[spaulding]

I'm fairly new to Sql Server and need some logic help. I'm trying to build a Job Assignment program and need to search my database to find people who haven't been assigned a job on a particular day, although they may have been assigned at an earlier or later date. Essentially, I'm searching for a list of people who don't have a record that meets my criteria. I need some pointers here and would appreciate any help

 

[jemminger]

sounds like you'd need something like

people_tbl
id
name

job_tbl
id
people_id
date

select
p.name
from
people_tbl p
left join job_tbl j
on j.people_id = p.id
where
j.date is null
or j.date <> '01/02/2005'

-jeff

http://www.jeffemminger.com

try { succeed(); } catch(E) { tryAgain(); } finally { rtfm(); }
i like your sleeves...they're real big

 

[spaulding]

I appreciate the quick response and your logic is virtually the same as mine. But I'm not sure that where statement is going to work in my scenario. Each Job assignment is for one day, so a given worker will have numerous job assignment records all (or all but one) of which will be <> requested date. I think your where statement will show all those records except for the record where that person is assigned on the requested date which would be a fatal error for the system. I need to be able to return a list of people_ids that have not been assigned and therefore have no record for that date.

My current line of thinking is to get a list of all workers then a second list of all workers assigned on that date and eliminate them from the first list leaving only available workers, but I'm not sure how to accomplish that. Again, I appreciate the help

 

[jemminger]

maybe something like

select
p.name
from
people_tbl p
left join job_tbl j
on j.people_id = p.id
where
j.id not in (
select id
from job_tbl
where date = '01/02/2005'
)


-jeff

http://www.jeffemminger.com

try { succeed(); } catch(E) { tryAgain(); } finally { rtfm(); }
i like your sleeves...they're real big

 

[spaulding]

Thank you Sir! Exactly what I needed. Didn't know you could nest Sql Statements. That makes a whole bunch of projects a lot easier.